January

Sometimes in a text string we are processing there is a tab, linefeed or carriage return character (or characters) in the text. Removing them can be challenging, but here is a piece of code that may help:

   * Check each byte in string until no more
     non-printable characters are found. A
     non-printable character is defined outside
     the ASCII set 32 to 126.;
   do i=1 to lengthn();
      if not(32<=rank(substr(,i,1)<=126) then
       substr(,i,1)=' ';
   end;

There is another way that is usually seen in references on the subject that use the NOTPRINT function that is quicker to process as it only does a subsitution if a non-printable character is found. For the purposes of this function the results depend directly on the translation table that is in effect (see TRANTAB= System Option in SAS National Language Support (NLS): Reference Guide) and indirectly on the ENCODING and LOCALE system options:

   do until(_flg=0);
      _flg=notprint();

      *If a non-printable is found, replace with a space;
      if _flg>0 then substr(,_flg,1)=' ';
   end;

Hope this is useful.

February

No names, we have all done it/seen it. An example of a specification:

   If not missing(cmstyy) & missing(cmstmm) & missing(cmstdd)
   & year consdt does not equal cmst_yyyy then set cenddt=last
   day of that year (which will be 31DEC).

And another example:

   GHR= (GHR_1 + GHR_2 + GHR_3 + SFR_33 + SFR_35 - 5) *5

When writing ADS specifications a good rule of thumb is to write it as if you were across the table from an FDA reviewer – these people on the whole do not know SAS or R or any of the other myriad of statistical software, do not know the structure of the datasets, and don’t have much time. The specifications also have to be written in a way that is not programming language specific, i.e. either a SAS or R or STATA or SPSS or, dare I say it, VB – a programmer, and not necessarily a SAS programmer, must be able to pick up the specification and get the same result.

There are some exceptions to this, namely when an obscure statistic is being requested and it needs a particular SAS procedure call with specific parameters. The other issue when writing language specific specifications is that it places huge onus on the one specifying to get it right, and yes, it CAN take days to rewrite code if the specification is found to need amendment.

In the first example, the specification is meaningless to a reviewer who has little time and/or knows little of a programming language. To a programmer, trying to work out what the intent is difficult at best and many will just take that specification and write code to it. In this snippet, Its intent was:

   If the conmed end date is a year value only, and the
   year value is not the same year as consent date, then
   set a computed end date of the end of the conmed year.

Within most programming groups it is expected that an intermediate level programmer and higher to get this intent and program it, and if they do not understand it they should ask another programmer for help - experienced programmers enjoy passing on their knowledge and experience when asked (maybe we are busy at that moment you ask, but give a little bit of time and we always come back and at least follow-up). Sometimes even a help from an internet search will assist.

As a side note, this specification was part of a larger set of specifications on working incomplete concomitant medication end dates -- what would have been ideal would be a general instruction in the programming specifications saying something like:

   Concomitant Mediation (CM) End Dates

   If a CM date is incomplete, the following rule will be set for a
   computed end date:

      o If the month and year is present then set computed date to
        the last day for the month
      o If only the year is present then set a computed date to the
        last date for the year.

Looking at the second example, a question could be raised when the specs are being reviewed over what happens with missing data points in a record - a look at an external reference found that the equation in the document catered for missing values, i.e. one or more of the five variables in the formula had a missing value, and additionally catered for the situation where there were less than half the scores present. Within most groups it is expected that a programmer to go to an external reference document (SAP, eCRF, protocol exempted) unless the specification directed so. As it happened this issue was detected and corrected early, but the worst case scenario could be that it be picked up by the client or outside reviewer.

What is some good guidance?

Specification writers, read the SAP and become familiar with it, and read the protocol if available. Write your specification as if you were talking to an outside reviewers making sure you specify the intent of the derivation - you can always point to an external document for that derivation if needed. Trust your programmers to take that specification and turn it into what you intended.

Programmers, before writing code, read the SAP and become familiar with it, and also read the protocol if available. If a specification is not clear ask another programmer for help - again, experienced programmers enjoy passing on their knowledge and experience. If it is still unclear, then take it back to the specification writer because if you don’t understand it, expect an outside reviewer not to understand it either.

Whether we are a specifications writer, programmer or statistical reviewer, the ultimate goal is to produce output that is clear, accurate and timely.

Hope this is useful.

March

Using the following data find the Number of Observations, Mean, Standard Deviation, Median, Minimum and Maximum values for the following data, grouping by Gender and age groups 18-64 and 65-75:

   ** DATA **
   SUBJECT  AGE  GENDER
   001      35   M
   002      70   M
   003      65   M
   004      40   F
   005      66   F
   006      36   F

   ** SAS PROGRAM **
   PROC FORMAT;
      VALUE agex
        18-64='18-64'
        65-75='65-75'
        other='Unexpected';
   RUN;

   PROC MEANS DATA=demog N MEAN STD MEDIAN MIN MAX;
      CLASS gender age; VAR age;
      FORMAT age agex.;
   RUN;

Using the AGE variable both in the CLASS and VAR statements it is treated both as a category and analysis variable. Note that the median calculation came into the MEANS procedure in SAS version 7 - prior to that the median could be calculated using the UNIVARIATE procedure.

See you next month.

April

There are many parts to a variable, key among them are the name of the variable (NAME), type (TYPE), and length (LENGTH). Under SAS version 8 and above the name of a variable can be 32 characters long, its type can be either Character or Numeric, and the length for a character variable can be set between 1 and 32767 characters while a numeric variable can be set between 3 and 8 (yes, some operating systems allow for 2 but not all).

There are other parts to a variable, but the three most common are the informat, format and label - we shall look at this last part this month and next.

What exactly is a label and why is it used? In simple terms the label is a short descriptive text giving a more user friendly description of the variable. Take for example a very simple example, the variable WEIGHT in a dataset - sure it tells me what the variable is (it is weight) but it does not tell me other things I may need to know like what unit it is in(inches) or when the weight data point was collected, all of this being very important information. Given that I am allowed 32 characters for a variable name I could change the variable name to reflect this information, but in most cases it is not possible to do this and it indeed impractical. So here comes the use of the LABEL statement.

The label itself can be 256 characters, including blanks, in length (40 characters if you are still using SAS version 6.xx) - this gives us plenty of room to write a good description of the variable. Setting the label is commonly achieved by using the LABEL statement inside a datastep or the LABEL option under the MODIFY statement in the DATASETS procedure, the syntax of which is given below:

   data class; *** Inside a datastep;
     set class;
     label weight='Weight (kg) at Start of Study';
   run;
   proc datasets library=work; *** DATASETS procedure;
     modify class;
       label weight='Weight (kg) at Start of Study';
     quit;
   run;

I personally use the datastep method if I am creating or modifying a variable inside that datastep, otherwise I use the DATASETS procedure, but is only my convention - there is no set rule with this.

Before going on to a real world example, lets first see how we would look at what labels are set, if any, inside a dataset. The two easiest ways to look at the data is to either run a CONTENTS procedure call on the dataset, or if you have the SAS Viewer installed, look at the attributes window for that dataset.

Now lets look at a real world example where it will all come clearer. For this example I will use the dataset SASHELP.CLASS. Lets look first at the structure of the dataset using the CONTENTS procedure (will actually make a copy first so I don't overwrite the original data):

   data class; *** Make a copy of the dataset;
     set sashelp.class;
   run;
   proc contents data=class;
    *** Get structure of the dataset;
   run;

Running this code we get the following output (abridged):

   Alphabetic List of Variables and Attributes

   #   Variable   Type   Len 

   3   Age        Num     8
   4   Height     Num     8
   1   Name       Char    8 
   2   Sex        Char    1
   5   Weight.    Num     8

If a label existed for a variable we would see a column headed "Label", but in this case there is no labels applied to the dataset. So now lets set one for WEIGHT as we indicated above (this time I shall do the content structure not from the CONTENTS procedure, but the CONTENTS statement inside the DATASETS procedure):

   proc datasets library=work;
     modify class;
       label weight='Weight (kg) at Start of Study';
     contents data=class;
     quit;
   run;

Running this code we get the following output (abridged):

   Alphabetic List of Variables and Attributes
 
   #   Variable   Type   Len   Label
   3   Age        Num    8
   4   Height     Num    8
   1   Name       Char   8
   2   Sex        Char   1
   5   Weight     Num    8     Weight (kg) at Start of Study

As you can see the variable WEIGHT now has a label - the reason why it is useful will become clear shortly. For the purposes of this example I will also now add a label to the variable AGE and HEIGHT using the DATASETS procedure as above:

   proc datasets library=work;
     modify class;
       label height='Height (in) at Start of Study'
             age='Age (years) at Start of Study';
     contents data=class;
     quit;
   run;

Note that I did not redo the label for the variable WEIGHT in the above code as it was already done previously, although I could have put in the step and even replaced it with new text.

Next month we use these labels and see what we can do with them.

May

From where we left off last month, now why is the label useful. As you can see already it gives a useful description of what the variable is. Now lets extend that to a small report using the PRINT procedure:

   proc print data=class noobs;
   run;

that produces the following output (abridged):

   Name       Sex    Age    Height    Weight
   
   Alfred      M      14     69.0      112.5
   Alice       F      13     56.5       84.0
   Barbara     F      13     65.3       98.0
   Carol       F      14     62.8      102.5
   Henry       M      14     63.5      102.5

The column headers are the same as the variable names, and we have the same problem of not knowing what the units are for Age, Height and Weight (but did we not put that in a label earlier) but lets add another option to get the labels:

   proc print data=class noobs LABEL;
   run;

that produces the following output (abridged):

                 Age (years)  Height (in)   Weight (kg)
                  at Start     at Start      at Start
  Name     Sex    of Study     of Study      of Study
 
  Alfred     M         14         69.0          112.5
  Alice      F         13         56.5           84.0
  Barbara    F         13         65.3           98.0
  Carol      F         14         62.8          102.5
  Henry      M         14         63.5          102.5

Now, as you can see, we have a report that gives a clear description of the variables though the use of dataset variable labels. There will be those reading this who will say that I could have put the label in the PRINT procedure call (yes, the LABEL statement is a global statement that can be used in almost any procedure) and would be done this way using the following code:

   proc print data=class noobs LABEL;
         label weight='Weight (kg) at Start of Study'
               height='Height (in) at Start of Study'
               age='Age (years) at Start of Study';
   run;

But the one reason I don't normally use this method is that the label does not carry forward in the dataset. Now see a complete example where the CLASS data is copied from the SASHELP directory, useful labels put on the dataset, then doing a PRINT, MEANS and TABULATE call on the same data, all with the same labels:

   proc datasets library=work;
      copy in=sashelp out=work;
         select class;
      modify class;
         label weight='Weight (kg) at Start of Study'
               height='Height (in) at Start of Study'
               age='Age (years) at Start of Study';
      quit;
   run;
   proc print data=class noobs LABEL;
   run;
   proc means data=class;
      var age height weight;
   run;
   proc tabulate data=class;
      class sex;
      var age height weight;
      tables age*
               (n*f=8.0 mean*f=8.2 std*f=8.3
                median*f=8.2 min*f=8.0 max*f=8.0)
             (height weight)*
               (n*f=8.0 mean*f=8.3 std*f=8.4
                median*f=8.3 min*f=8.1 max*f=8.1),
             sex all='Total';
   run;

produces the following output (abridged):

                 The PRINT Procedure

                    Age (years)    Height (in) Weight (kg)
                      at Start       at Start   at Start
   Name      Sex      of Study       of Study   of Study

   Alfred     M          14            69.0          112.5
   Alice      F          13            56.5           84.0
   Barbara    F          13            65.3           98.0


                  The MEANS Procedure

   Var    Label                         N Mean STD Min Max      
   Age    Age (years) at Start of Study 19  13  1   11  16
   Height Height (in) at Start of Study 19  62  5   51  72
   Weight Weight (kg) at Start of Study 19 100 23   51 150


                The TABULATE Procedure

   ----------------------------------------------------
   |                       |       Sex       |        |
   |                       |-----------------|        |
   |                       |   F    |   M    | Total  |
   |-----------------------+--------+--------+--------|
   |Age (years)|N          |       9|      10|      19|
   |at Start of|-----------+--------+--------+--------|
   |Study      |Mean       |   13.22|   13.40|   13.32|
   |           |-----------+--------+--------+--------|
   |           |Std        |   1.394|   1.647|   1.493|
   |           |-----------+--------+--------+--------|
   |           |Median     |   13.00|   13.50|   13.00|
   |           |-----------+--------+--------+--------|
   |           |Min        |      11|      11|      11|
   |           |-----------+--------+--------+--------|
   |           |Max        |      15|      16|      16|
   |-----------+-----------+--------+--------+--------|
   |Height (in)|N          |       9|      10|      19|
   |at Start of|-----------+--------+--------+--------|
   |Study      |Mean       |  60.589|  63.910|  62.337|

Note that the label we defined in the DATASETS procedure carried forward to the PRINT, MEANS and TABULATE procedures.

Hope this helps.

June

An intriguing question came regarding a message appearing in the SAS LOG when creating a SAS Transport File using the XPORT engine:

   WARNING: Engine XPORT does not support SORTEDBY operations.  SORTEDBY information cannot be
   copied.

The message appears because the dataset was created with a sort by variable list and is in the descriptor, e.g. created by

   PROC SORT DATA=_final OUT=ads.adsl;
      BY usubjid;
   RUN;

with the SORTEDBY value being USUBJID. SAS actually knows a lot about your data, including if there is an order to it, when it loads. It is so smart that it even knows how many observations are in the dataset without even loading the entire dataset! Anyway, getting back, SAS transport files using the XPORT engine do not recognize the SORTEDBY descriptor, and all the message indicates that it does not recognize it. The WARNING message can be ignored, but an easy way to get around it is just add a data step before creating the SAS Transport Fike with something like:

   DATA ae;
      SET ae;
   RUN;

Hope this helps.

July

I am starting a series of ten Tip of the Month' that are short and to the point.

An easy way to sort a mixed case variable as if it were a variable with only upper case text, without creating a new variable, is to use PROC SQL as shown below:

  proc sql;
    create table _class0 as
      select * from sashelp.class
      order by upcase(name);
    quit;
  run;

Hope this helps.

August

Use the NOEQUALS option on PROC SORT to reduce computer time and memory. By default SORT keeps the same order of the observations with identical BY variable values as they were in the input data set. The NOEQUAL option tells SAS to ignore the order of observations within BY groups.

  proc sort data=sashelp.shoes out=shoes noequals;
    by region;
  run;

Hope this helps.

September

To remove page breaks and put dashes (-) across the page on output from PRINT, MEANS, FREQ and UNIVARIATE procedures, for example, do:

  options formdlim="-";

To reinstall page breaks (there is no space between the quotes):

  options formdlim="";

Hope this helps.

October

The DETAILS option lists the number of observations, the number of variables, and the label of the data set, and helps in finding issues with variable names in the programs.

  proc datasets library=work details;
    contents data=sashelp.class;
  run;

While this information is available within the CONTENTS statement, the first page will list this information of any dataset that is processed in the DATASETS procedure.

Hope this helps.

November

It is possible to mimic PROC MEANS using PROC SQL, as shown in the following code:

  proc sql;
    select sex, age,
      count(weight) as n,
      avg(weight) as avg format=6.2,
      std(weight) as std format=6.3,
      min(weight) as min,
      max(weight) as max,
      from sashelp.class
      group by sex, age;
    quit;
  run;

The example above does analysis by SEX and AGE. But what is missing? MEIDAN! Fortunately from SAS version 9.4 and beyond there is a function called MEDIAN, and just to add it to the list above just put in the following line:

  median(weight) as med format=6.2

Hope this helps.

December

To put the variable names and/or values in TITLE, use #BYVAR(byvariable) for the BY variable's name and #BYVAL(byvariable) for the BY variable's value. The statement OPTIONS NOBYLINE; eliminates the default by lines.

  options nobyline;
  proc print data=class;
    title "Student Demographics by #byval(sex)"; by sex;
    pageby sex;
    var name age height weight;
  run;

Hope this helps.