January

So you have some new data and you just want to see there is more observations than last time -- maybe some thought the the data was smaller or the same?

Here is a little piece of code that will help -- I have two directories of data, both referenced by the libnames TUAI_NEW and TUAI_OLD:

   *Setup and set libnames for the old and new data;
   options date;
   libname TUAI_OLD "C:\TUAI\DATA\OLDDATA";
   libname TUAI_NEW "C:\TUAI\DATA\OLDNEW";
   run;
   
   *Get Dictionary information from VTABLE in the SASHELP directory for the two libraries
    keeping only the number of observations;
   proc sort data=sashelp.vtable out=_old0 (keep=memname nobs);
      by memname;
      where libname='TUAI_OLD';
   proc sort data=sashelp.vtable out=_new0 (keep=memname nobs);
      by memname;
      where libname='TUAI_NEW';
   run;
   
   *Merge the two sets of information renaming the number of observations in the
    TUAI_NEW library;
   data _all0;
      merge _old0 _new0 (rename=(nobs=_nobs));
      by memname;
   run;
   
   *Use PROC PRINT as a tool to generate the report;
   title1 "Quick Compare of Old Data vs New Data";
   proc print data=_all0 label noobs;
      var memname nobs _nobs;
      label memname='Dataset'
            nobs='Obs in Old Data'
            _nobs='Obs in New Data';
   run;

This piece of code will write a quick report of the number of observations of each dataset in the two libraries referenced.

Hope this is useful. See you next month.

February

When we bring in multiple sets of data with a common ID, for example PATIENT_ID, sometimes we want to subset the data to a particular set of IDs. Rather than writing the same WHERE or IF statement multiple times there are a few techniques for writing it just once -- this allows for easy changes to the list of section as it only has to be changed once. See below for a code example:

   %macro subsel;
      where PATIENT_ID in('001','002','005')
   %mend subsel;

   proc print data=demog;
      %subsel;
   run;
   proc print data=ae;
      %subsel;
   run;

Hope this is useful. See you next month.

March

Occasionally we have to produce a set of listings for each subject, so if you have fifty subjects you have to produce fifty different sets of listings based on all the datasets. Here is one technique that works.

First, find the unique set of subjects being processed, usually from a list like a DEMOG list, using the following code:

   proc sql noprint;
      select distinct PATIENTID
         into: patlist separated by ' '
         from DEMOG;
      quit;
   run;

The SQL has now created a unique set of subjects, which will be the list of subjects that we use to create the outputs.

Now lets get the program to produce the output:

   %macro prtmylst;
      %let i=1; *Use an internal counter;
      %do %while(%scan(&patlist,&i) ne ); *Go though the list and do an action until there is no more;

         proc printto print="C:\TUAI\PATLIST\%scan(&patlist,&i).LST";
            *Creates out output file, same name as PATIENTID value;
         run;

         *DEMOG and AE data, for this example, using PROC PRINT, but could easily be PROC REPORT;
         proc print data=demog noobs;
            where PATIENTID="%scan(&patlist,&i)";
         run;
         proc print data=ae noobs;
            where PATIENTID="%scan(&patlist,&i)";
         run;

         proc printto; *Close the existing destination;
         run;

         %let i=%eval(&i+1); *Get next subject on the list;

      %end;
   %mend prtmylst;
   %prtmylst;
   run;

As you can see this goes though the list and creates a set of PROC PRINT listings for each subject. This can be extended to use for PROC REPORT amound a number of different other procedures.

Hope this is useful. See you next month.

April

A short tip this month. Often we have a summary of statistics with one record that we want to merge on to a dataset with, lets say, monthly records. The following DATA step takes the data in SUMMARY, with MONTHLY data to produce a COMBINED dataset with all the data:

   data combined;
      if _n_=1 then set summary;
      set monthly;
   run;

Hope this is useful. See you next month.

May

I recently had to solve an issue where the output dataset did not contain all the variables that the KEEP statment produced -- by default a WARNING message at least should have come out in the SAS LOG, but it was not there.

One of those options that is not used very often is the DKROCOND options which specifies the level of error detection to report when a variable is missing for an output data set during the processing of a DROP=, KEEP=, or RENAME= data set option -- by default it is WARN but other values can bee ERROR or NOWARN. In the case I had to look at the option had been set to NOWARN, used by a programmer to deveoplment to avoid the SAS LOG containing WARNING messages.

While there are cases where options are set for good reason, other options should be treated with caution, this being one of them. The moral of the story, don't change default system options without good reason.

Hope this is useful. See you next month.

June

Did you know that the IN operator can use ranges of values? Here is an example:

   if mcode in(1,2,3,4,7,8,9,11) then do;

but this can be shortened to

   if mcode in(1:4,7:9,11) then do;

It does save a bit of time on coding.

Hope this is useful. See you next month.

July

Your program has a variable that is coded as 1=YES and 2=NO with an associated format called YN. Now there is a new value of 3=DON'T KNOW in your data but the format YN has not been changed. In your reporting program you can create another format, in this example YNX, using the following code:

   proc format;
      value ynx
         1='YES'
         2='NO'
         3="DON'T KNOW";
   run;

A better way though, avoiding transcription problems, is to make the new YNX format by nesting the old format YN into the new as shown in the following code:

   proc format;
      value ynx
         3="DON'T KNOW"
         other=[yn.];
   run;

Note that you must enclose the existing format name in square brackets, as shown above, or with parentheses and vertical bars, for example (|yn.|).

Hope this is useful. See you next month.

August

Like a builder comes to work with some tools, a SAS Programmer carries some tools as well. The three key tools I consider most important are a notebook (taking notes of that piece of code that you found interesting), SAS manual(s) in PDF format, a simple text editor and a LOG Checker. The last one may seem strange but the SAS LOG has everything about the SAS Program that ran, right on down to number of observations used in each step. But the SAS LOG can be long, and manually checking such a file is next to impossible, hence an electronic checker.

Below is a SAS program that I use (I have a slightly more advanced version but this is Old Faithful) reproduced below:

*Define location of LOG Files to process;
%let logdir=%str(C:\temp\tables);

*Bring in ALL SAS LOG(s);
data _alllogs0;
   attrib _txt length=$256 informat=$char256.
   format=$char256.
   _fn length=$256
   _myinfile length=$256
   _ln length=8
   _myinline length=8;
   infile "&logdir.\t*.log" /*Here use t*.log for all table LOGS but can be anything e.g. l*.log */
      lrecl=256 filename=_myinfile
      line=_myinline length=len;
      input _txt $varying256. len;
   _fn=scan(strip(_myinfile),-1,'\');
   _ln=_n_;
proc sort data=_alllogs0;
   by _fn _ln;
run;

*Output findings;
title1 "QC of SAS LOG(s)";
data _null_;
   retain _k 0; *Internal counter;
   file PRINT;
   set _alllogs0 end=eof;
   by _fn _ln;
   if first._fn then do;
   put // @1 '***** LOG FILE: ' _fn /;
   _k=0;
   end;
   if index(_txt,'ERROR:') or
     index(_txt,'WARNING:') or
     index(_txt,'uninitialized')
     then do;
      _k+1;
      put _txt;
   end;
   if last._fn and _k=0 then
     put @1 '** NO ISSUES FOUND **';
   if eof then put / '/*EOF*/';
run;

This macro is different from most in that it will do a whole directory of SAS LOGs , not just one file (but this can be specified in the FILE statement in the first datastep).

The aim in the final production version is to have '** NO ISSUES FOUND **' when this is found for every program, and this along with the SAS LOGs be placed in the final documentation for the TLF programming. It may not always be possible to achieve but documentation should be given explaining where it is not. If you are writing a WARNING message to the SAS LOG, write:

   'WAR' 'NING: Issue with program';

and the word ‘WARNING:’ will not be printed out to the SAS LOG unless there is an issue.

To give an example, I recently dealt with an issue where in the SAS LOG was the following message:

   WARNING: A character that could not be transcoded was encountered.

This message was produced in the SAS LOG for several runs previously but was either not found earlier, found and ignored, or found and thought to be okay. As it turned out, SAS thought the data coming in was in one format but was actually in another format resulting in the data not being read correctly from the file.

Hope this is useful. See you next month.

September

When running a SAS program in Batch mode, SAS does produce a lot of output in the SAS LOG. There is a temptation to limit this output so the SAS LOGs are not so big by turning off the MPRINT, MPRINTNEST, MLOGIC, MLOGICNEST, SYMBOLGEN and SOURCE2 options. However, in doing so you limit the documentation saying what the program did at each stage – it is impossible to say with accuracy what code generated which message in the SAS LOG. A good auditor will ignore the SAS program code and go directly to the SAS LOG when looking at when the program was run and what the code was – program code can be easily manipulated, SAS LOGs are more difficult.

Best practice, turn these options on and produce as much as possible to the SAS LOG.

Hope this is useful. See you next month.

October

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 that replaces non-printable characters with a blank:

.  * Verify each byte in string until no more non- printable characters are found;
   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. See you next month.

November

We have all done it/seen it. An example of a specification:

   If raw.repeat_data_cm_rg.cend_precision=1 and
   init_date_ser>=inception_date and raw.repeat_data_cm_rg.cend
   not missing and raw.repeat_data_cm_rg.cend_year not equal to
    year(ads.a_pat_demo.index_date) then set cend=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:

Within many 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 was raised when the specs were 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 not 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 have been that this would have been picked up by the client or outside reviewer.

Yet another cautionary tale. This was another study which included some ADS specs with SAS code included. The programmers had happily programmed away and output was out when a question arose on how so few AE records met a certain criteria. After a quick investigation it was found that one of the key variables in the AE ADS, a flag indicating whether an AE was treatment emergent or not, did not match up with the SAP – this required a fix to the ADS program concerned, a rerun of the ADS and output programs.

Reading and understanding the SAP cannot be overstated – it is a must, after all this is the programming and statistical blueprint for the study. That is not to say there are issues with the SAP (saw an example where AGE in years of a subject was computed as the number of days from randomization to date of birth / 12) but this is the agreed analysis for the study between us and the client.

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, for us and our client.

Hope this is useful. See you next month.

December

Every so often it is important to remember regarding general programming standards and expectations in clinical trail programming.

First topic:

ALWAYS review the Protocol, SAP including table shells, eCRF (if present), ADS and APS, and any other source documentation for background information or specifications

It is not good programming practice just to work off any ADS or APS specifications alone as these may be incorrect – the controlling document for any programming is the SAP. This is usually in an SOP.

Second topic:

ALWAYS do your work on the network drive in the relevant project area

This includes both development and production phases of the programming. The two main reasons for this is that the:

• Network drives are usually backed up
• Backup programmers can take over should you be out of the office for some reason, including illness, or on another more important project

The project area is backed up every night so that if your laptop suddenly fails the work is not lost, or if you are not at work for a day due to illness and something needs to get out then someone can take over and complete it. On a personal note, I did work in a company many years ago where one of the programmers was doing some work on their own laptop, against policy, and while they were walking through a door and the door closed on them knocking the bag causing the heads of the hard disk to be put out of alignment resulting in critical work being lost.

While at this time it is strongly advised that all programming work be on the network drive in the relevant project area, it will shortly become department operating policy. It is not enough to store your programs in your private network area as other programmers cannot get to the programming.

If you are concerned that a program in development will be run by another programmer as production then one suggestion is to surround the whole code as a macro that does not resolve, e.g.

   %macro skip;
      SAS Code;
   %mend skip;

Third topic:

ALWAYS run your SAS program in Batch Mode

When you run a program in batch mode several things happen including the creation of a SAS LOG of the program, which shows who ran the program, when and what happened and makes certain that there are no extraneous settings that can exist when running in interactive mode. From a QC/QA standpoint the SAS LOG is more important than the SAS Program as it says what it did and it should be possible to recreate the SAS program from the SAS LOG.

The SAS LOG must be checked for ERRORS and WARNINGS at a minimum but also other text could and should be searched for, e.g. unitialized variables. Also check the output – occasionally there will be issues not detected in the SAS LOG where the output either has a dataset with zero observations or a table with blank pages.

Hope this is useful. See you next month.