January

Knowing how many observations exist from a dataset is sometimes very important as it could dictate what action is needed in the next step. There are a number of ways to do this but one way is to use a macro varible created when a SQL Query is made called SQLOBS. Below is an example of its use:

  *-----------------------------------------------------*
   Get the number of female students from SASHELP.CLASS
  *-----------------------------------------------------*;
  proc sql noprint;
    create table _dat0
      select *
      from sashelp.class
      where sex='F';
    quit;
  run;
  %let numobs=&sqlobs;

Hope this helps.

February

It is possible to create a directory within a SAS program on some operating systems using the DLCREATEDIR option, available since SAS 9.3. Why is this useful? It is easy to create a standard set of directories easily, as shown in the following example:

  options DLCREATEDIR;
  %let basedir=%str(c:\tuai);
  libname rawdata "&basedir.\data\rawdata";
  libname sdtm "&basedir.\data\sdtm";
  libname adam "&basedir.\data\adam";
  libname psdtm "&basedir.\programs\sdtm";
  libname padam "&basedir.\programs\adam";
  libname listings "&basedir.\programs\listings";
  libname tables "&basedir.\programs\tables";
  libname graphs "&basedir.\programs\graphs";
  libname pmacros "&basedir.\programs\macros";
  run;

The program above will create the directories:

  c:\taui
  c:\taui\data\rawdata
  c:\taui\data\sdtm
  c:\taui\data\adam
  c:\taui\programs\sdtm
  c:\taui\programs\adam
  c:\taui\programs\listings
  c:\taui\programs\tables
  c:\taui\programs\graphs
  c:\taui\programs\macros

It is possible to use concatenated to libnames to also do this, as shown in the following example, to create the data and sub-directories:

  options DLCREATEDIR;
  %let basedir=%str(c:/tuai);
  libname data ("&basedir./data" "&basedir./data/rawdata" "&basedir./data/sdtm" "&basedir./data/adam");

If you get a note indicating that the option DLCREATEDIR is restricted at your site, this means that your administrator has restricted you from creating directories -- in this case you have to talk to your IT department.

Hope this helps.

March

DO loops with an index variable is something that is a basic task in our code, e.g.

  data _null_;
    do k=1 to 10;
      put k=;
    end;
  run;

This will simply put out a set of numbers in the SAS LOG from 1 to 10.

We can also use a BY option to step the count, for example:

  data _null_;
    do k=1 to 10 by 2;
      put k=;
    end;
  run;

This will produce the values 1, 3, 5, 7 and 9 in the SAS LOG.

Another form is without the 'TO' that uses set values, as the folowing example shows:

  data _null_;
    do k=1,7,5,2,9;
      put k=;
    end;
  run;

This will put out a set of numbers in the SAS LOG of 1, 7, 5, 2 and 9. But you are not restricted to numbers, you can also do character values, as the following example shows:

  data _null_;
    do name="Luna","Milo","Bella","Willow";
      put name=;
    end;
  run;

This will put out the set of names to the SAS LOG of "Luna", "Milo", "Bella" and "Willow".

Hope this helps.

April

Here is a macro that is so simple but yet so useful that I keep it in my toolkit. What this does just print the first number of observations (default 10) in a dataset -- useful for just finding out what is in the dataset I am using.

  %macro prttop(dsn=%str(sashelp.class)
               ,num=10
               );
    proc print data=&dsn (obs=&num) width=minimum;
    run;
  %mend prttop;

Running this with the default options, i.e.

  %prttop;

will present the first ten observations in the dataset SASHELP.CLASS.

Hope this helps.

May

Back in March we had a quick look at DO loops with and saw that we can place character values in the DO loop. This month we will take a quick look at the DO OVER. This is not one that is commonly used but it is useful to know.

The DO OVER arrayname is equivalent to a DO i=1 TO DIM(arrayname). An example of its use is:

  data _null_;
    set sashelp.class;
    array zaz height weight age;
    do over zaz;
      if missing(zaz) then zaz=.M;
    end;
  run;

The above will go through each record, looking at the variables HEIGHT, WEIGHT and AGE, and if missing set to '.M' a value that we will represent as missing.

Another more common way you will see this is with the DO ... TO statements as shown below:

  data _null_;
    set sashelp.class;
    drop i;
    array zaz(*) height weight age;
    DO i=1 TO DIM(zaz);
      if missing(zaz(i)) then zaz(i)=.M;
    END;
  run;

The use of the DIM function tells the DO loop how many variables that are being used in the array. Whether you like one or the other form is up to personal preference but the DO ... TO ... form is a little more contolling since you can specify what variables in the array to process depending on some specified condition, e.g.

  data _null_;
    set sashelp.class;
    drop i;
    array zaz(*) height weight age;
    DO i=1 TO DIM(zaz);
      if missing(zaz(i)) then do;
        if i=3 and missing(zaz(i)) then call missing(zaz(1),zaz(2));
      end;
      else zaz(i)=.M;
    end;
  run;

If variable AGE is missing then it will set WEIGHT and HEIGHT to missing, else it will set WEIGHT, HEIGHT and AGE to '.M'.

Hope this helps.

June

This is something that may have been mentioned before but it is worth mentioning again.

When data comes there are sometime characters that are in the data which cannot be printed, e.g. carriage returns or tab characters. How do we fix this? Two easy sollutions are possible.

First is use the COMPRESS funtion with the 'kw' modifier, e.g.

  data class;
    set sashelp.class;
    name=compress(name,'kw');
  run;

Second is a little more complicated, but it is more controllable, and uses the RANK funtion. Most systems and applications will handle the ASCII characters 32 through 126 inclusive, which is the standard set of LATIN1 characters -- it is possible to adapt this for your particular situation and characters. The code in this case would be something like:

  data class;
    set class;
    do _k=1 to lengthn(name); *Look at every character in string variable NAME;
      if not(32 le substr(rank(name),_k,1) ge 126) then substr(name,_k,1)='$';
        *Replacing 'bad' character something to later remove;
    end;
    name=compress(name,'$'); *Remove 'bad' characters;
  run;

Hope this helps.

July

Back in March we had a quick look at DO loops with and saw that we can place character values in the DO loop. This month we will take a quick look at WHILE and UNTIL options.

The DO WHILE condition continutes to execute statements in a DO loop while a condition is true, checking the condition BEFORE each iteration of the DO loop. An example of this is:

  data _null_;
    k=1;
    do while (k<8);
      put k=;
      k+1;
    end;
  run;

This will put out a a set of numbers in the SAS LOG of 1, 2, 3, 4, 5, 6 and 7 -- it fails when the index variable k is 8.

The DO UNTIL condition continutes to execute statements in a DO loop while a condition is true, checking the condition AFTER each iteration of the DO loop. An example of this is:

  data _null_;
    k=1;
    do until (k=8);
      put k=;
      k+1;
    end;
  run;

This will put out a a set of numbers in the SAS LOG of 1, 2, 3, 4, 5, 6, 7 and 8 -- it fails when the index variable k is 8 but still puts out the value '8' as it does not end until the END of the DO loop.

Using the WHILE or UNTIL options can result in an infinite loop where the condition is and never can be met. Remembering that for TRUE and FALSE is represented by 1 and 0 respecively, using DO UNTIL(0) or DO WHILE(1) will result in an endless loop, or something simple as DO I=1 BY 1 will cause the same -- to stop this we will look at some techniques later in the year.

Hope this helps.

August

Preventing SAS data sets from accidentally being replaced is something that is necessary. Some use OS protections, as Windows and Linux/Unix provides, and some use what SAS provides.

At a bare minimum the SAS option NOREPLACE should be used to prevent a permanent SAS data set from being accidentally replaced with another same-named data set. To replace it, use the REPLACE SAS option in your code and this will replace it.

The REPLACE | NOREPLACE option can be specified in an OPTIONS statement, in the OPTIONS window, at SAS invocation, or in the configuration file.

Sometime down the line we will discuss other potential possibilities for protecting your dataset using SAS.

Hope this helps.

September

Before a dataset is sent to a permanent area we often want to rename, drop or modify variables, including the order. Unfortunately SAS PROC DATASETS and SAS PROC SQL; ALTER statements can only rename or modify a dataset variable, but cannot drop it, or conversly keep a set of varibles. We also want to make certain the variables are in a particular order.

You can certainly do something like:

  data final;
    attrib name length=$40 label='Name'
           htin length=8 label='Height (in)'
           wtlb length=8 label='Weight (lb)';
    set sashelp.class (rename=(height=htin weight=wtlb));
    keep name htin wtlb;
  run;

This certainly works BUT it means typing some variable names in multiple times. What about PROC DATASETS?

  proc datasets library=work nolist nodetails nowarn;
    append base=final data=sashelp.class (keep=name height weight);
    modify final;
      rename height=htin weight=wtlb;
      label htin='Height (in)' wtlb='Weight (lb)';
    quit;
  run;

The PROC DATASETS solution does not allow us to reposition a variable in a SAS dataset as does the datastep, but does have fewer cases where we have to write the dataset variable multiple times. But for efficiency, how can we only write a variable once? Enter stage left, PROC SQL, as the following example shows:

  proc sql;
    create table final as
      select name,
             height as htin label='Height (in)',
             weight as wtlb label='Weight (lb)'
      from sashelp.class;
    quit;
  run;

As noted in the PROC SQL we only mentioned variables once, not multiple times as in the DATASTEP example.

You do not need to be a SQL expert to use SAS, infact in some cases it hinders things by those who do not know SQL past a basic level, supporting the code. However, a good SAS programmer does need to have some knowledge of what proc SQL can do to do their work efficiently.

Hope this helps.

October

An infinite loop is easy to create. Lets look at a few examples:

  do k=1 by 1;
    ... more SAS statements ...
  end;

or

  do k=1 to 5 by 2;
    ... more SAS statements ...
    k=1;
  end;

So how to prevent this? Coding correctly would prevent this but there are two SAS statements that could help:

• LEAVE: Stops processing the current loop and resumes with the next statement in sequence
• CONTINUE: Stops processing the current DO-loop iteration and resumes with the next iteration of the DO-loop

Lets now look at some examples where these statements are used. Our first example uses a counter and if the loop goes beyond an impossible value, in this case 1,000,000, it exits the loop:

  do k=1 by 1;
    ... more SAS statements ...
    counter+1;
    if counter>1000000 then leave; *Some impossible number;
  end;

To use the opposite statement, CONTINUE, the loop will iterate until the counter condition is met:

  do k=1 by 1;
    ... more SAS statements ...
    counter+1;
    if counter<1000000 then continue; *Some impossible number;
  end;

Should you use a LEAVE or CONTINUE inside a finite loop? Probably not but lets look at an example anyway:

  do k=1 to 50 by 1;
    ... more SAS statements ...
    counter+1;
    if counter>1000 then leave; *Some impossible number;
  end;

In the example above the counter just makes sure that in the almost impossible situation where the loop does not stop the LEAVE statement will stop the loop.

Hope this helps.

November

In the datastep which is first -- WHERE, KEEP/DROP or RENAME? Think alphabetical. First is the DROP/KEEP, the RENAME then the WHERE. So you could see a piece of code that looks like:

  data final;
    set sashelp.class;
    keep name height weight;
    rename height=htin weight=wtlb;
    where htin ge 65 and wtlb ge 50;
  run

which would create a dataset FINAL with the variables NAME, HTIN and WTLB, and subset where HTIN ge 65 and WTLB ge 50.

Hope this helps.

December

Sometimes we want to get a list of variables to keep out of a "next to final" dataset to add to that KEEP statement. Rather than write it out, a quick way is to do it from PROC CONTENTS as the following code will demonstrate:

  proc contents data=prefinal varnum SHORT;
  run;

This will create a list of variables in the .lst file from the dataset in variable number order. An example is the following:

  options nocenter nodate nonumber;
  proc contents data=sashelp.class varnum short;
  run;

will create

  The SAS System

  The CONTENTS Procedure

  Variables in Creation Order

  Name Sex Age Height Weight

To select the variables for your KEEP statement, just open the .lst file, copy and paste the list of variables to the program and then edit.

Hope this helps.