SAS Macro functions and returning values and a bit about macro variable type casting

I wrote an article about this subject before but I was quite new to SAS and it was quite confusing so I'm writing it again better and clearer this time.

The SAS macro programming language is a strange language lacking data types we usually associate with strongly or even weakly typed programming languages. SAS macro variables are intrinsically treated as strings. The equivalent command for casting a type from one to another is an input() command. For example, if I have a string that contains the month and year (MMMYY), then I can convert it to a SAS date format like this:

%let mmmyy = JAN11;

%let datum = %sysfunc(input(&mmmyy., MONYY.)); 

*the second argument defines the format of the data (&mmmyy.) that you want to cast into SAS format. &datum. now holds the SAS date equivalent of 01/01/2011;

Datum now holds the SAS date format of the first of January 2011. SAS macro variable values like the integer date stored in datum are intrinsically stored as strings, so you can't just do anything with them. In order to treat them as an actual integer and do any mathematical operations on them you need to use eval() like so:

%let yesteryear = %eval(&datum. - 1); *holds the SAS date for 31/12/2010;

One problem often encountered with SAS macro variables is when working with dates. Often, people define a date like so:

%let bankholiday = "03JAN11"d;

This is acceptable, but if you try to do any mathematical operations using that variable outside of a data step it will throw an error message and literally show that you tried to carry out an operation on the string ' "03JAN11"d '. The best ways to convert such a date to the SAS date format is to use either intnx() or define the date using mdy():

%let bankholiday = %sysfunc(mdy(01, 03, 11));

or

%let bankholiday = %sysfunc((intnx(day, "03JAN11"d, 0)));

I've yet to figure out how to detect the type of the macro variable before I do anything with it, so I advise you to inform other programmers about the type of the variable your code works with before you let someone else use your code.

Now. When it comes to macro functions, SAS also proves to be strange when compared to other languages. What is a very natural and normal way of working in a procedural/Object Oriented language appears to be missing in SAS. I've yet to see an official documented example that shows a macro returning a value, but it is possible. I doubt I'm the first person to discover or use this method.

What is odd about macro functions that return values is that you should NEVER use single line comments inside the body of the macro otherwise it throws an error! Always use the multi-line comments like /* this comment */. And there is no "return" command preceding the variable to make it return but there is a %return statement and all this does is stop the execution of the rest of the code and jump out of the macro. The way to return a value is to just write the the name of the variable holding the value you wish to return, without a semicolon on the end.

Anyway. Here's an example bit of code that lets you ask if a date is a weekend or not. If it is a weekend it returns 1 else it returns 0.

/**
* %isWeekend(datum)
* Tells you if a date is a weekend or not by returning 1 (true) or 0 (false). If you leave it empty it will check todays date.
* Usage:
%let datum = %sysfunc(mdy(01,01,2011));
%put %isWeekend(&datum.); *shows 1 (true);
*
* @return    boolean    If the date you supplied is a weekend it will return 1, otherwise 0.
* @param    date    date    The date you want to check. If you leave this empty it will use todays date
* @date 20110105
* @author Ahmad Retha
**/

%macro isWeekend(date);
    %if date=  %then %do;
        %let date = %sysfunc(today());
    %end;

    %let wd = %sysfunc(weekday(&date.)) ; /*find the weekday of the date given. Sunday=1, Saturday=7*/

    %let iwe = 0; /*set the variable we wish to retune to 0 (false) initially*/
    %if &wd.=1 or &wd.=7 %then %do;
        %let iwe = 1;
    %end;

    /* return iwe (1 or 0) */
    &iwe.
%mend;


Allow me to explain what this code does. The first part of the code, the %if statement, checks to see if a date argument was supplied and if it isn't it uses today's date. This behaviour of setting an empty parameter to a default value makes our macro function more robust and easy to use - it is a recommended practice.

The next section assigns the weekday, Sunday, Monday, Tuesday through to Saturday to the variable &wd. as a number from 1 to 7, as there are 7 days in a week. In SAS, the week starts on Sunday, 1, and ends on Saturday, 7.

Next we create a macro variable, iwe, which holds the value we wish to to return. We want to return 1 (true) if the date is a weekend, or 0 (false) if it's not. Initially we set the value to 0 (false) as most days of the week return false.

The next part checks if the weekday is Sunday (1) or Saturday(7) and if the date's day is a weekend it sets iwe to 1 (true). If it's not a weekend then iwe already holds 0 (false).

Finally, we return the value held in iwe (either 1 or 0) by just writing the variable &iwe. by itself - note that you should NOT put a semicolon on the end otherwise it will throw an error. There is no "return" command - just put the variable name on a line of its own.

That's it. Easy right?

You can write many really useful macro functions that you will use often and stick them into a file and make a library of useful functions to %include and use in your projects. For example, for my job I wrote a macro called %isWeekend(date), like the one above (though as I'm writing this from home I couldn't just copy and paste and I wrote the above on the fly from memory), another macro called %getNext(day) which returns the date of the next weekday you give it, %getLast(day) which is similar but looks backwards, %isHoliday(date) and %getLastWorkday(date). I stuck those into a SAS file, a library, and now include it into my other scripts when I need the functionality. This approach promotes code re-use and makes coding quicker and easier and as there is less duplicate code it is easier to maintain. Naturally, all my code is highly documented and I recommend you comment up your code as well.

I hope this little tutorial proves useful.

- Ahmad Retha

Difficult SAS issue - restructuring datasets with proc transpose

I've been trying to solve this problem for a few hours and have tried many different things. In the end, it was two sets of a process that solved the problem with proc transpose. It's worth documenting so I'm blogging it.

The Problem:

I have an example dataset that contains observations like so:


(dataset: dayobs)
days_range,    value,    count
0-9,    300,    6
60-69,    250,    4
300-309,    76,    1

I wanted it to show all the ranges with value and count set to zero if it wasn't already set:

days_range,    value,    count

0-9,    300,    6

10-19,     0,     0

20-29,     0,     0

30-39,     0,     0

40-49,     0,     0

50-59,     0,     0

60-69,    250,    4

... etc ... 

290-299,    0,    0

300-309,    76,    1

That was easy, simply make a dataset that lists the day_range from 1-10 ... 390-400.

data ranges;
    input days_range $;
    format days_range $7.;
    datalines;
0-9
10-19
... etc ...
    ;
run;

and then merge both the datasets - dayobs and ranges - to create a new dataset called daysrange:

data daysrange;
    merge ranges dayobs;
    by days_range;
run;


OK. Now, I wanted to transpose it into a single row so I can insert that into a big dataset that logs the changes every day. I want to make it look like so:

v1_9, c1_9, v10_19, c10_19, v20_29, c20_29, ...etc... v60_69, c60_69, ...etc
300, 6, 0, 0, 0, 0, ...etc... 250, 4, ...etc

It might seem obvious what to do but I tried to be clever and did this:

data rowifieddaysrange;
    set daysrange;


    if days_range='0-9' then do;
        c1_9=count;
        v1_9=value;
    end;
    else if days_range='10-19' then do;
        c10_19=count;
        v10_19=value;
    end;
 etc...


keep c1_9 v1_9 c10_19 v10_19 etc...;
run;

and this is what the data looked a bit like in the end:

0, 0, 0, 0, 0, 300, 0, 0, 0, 0, 0, 0, 6
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
250, 0, 0, 0, 0, 0, 4, 0, 0, 0, 0, 0, 0
etc.

Basically, it had the correct columns, but they were spaced out across different rows. No matter what I tried - I look at all the different sas procs and their options - I was unable to collapse/compact it into one row... if anyone knows the solution to this problem I'd really like to see it please. Ta.

OK. So guess what the solution is... It turns out you need to use two sets of proc transpose and a couple of merges, to deal with each variable individually - then finally merging the two separate datasets together at the end! :


*transpose the daysrange dataset first by count;
proc transpose data=daysrange out=tdaysrange(drop=_:) prefix=c;
    id days_range;
    var count;
run;
*now transpose the table with all the categories;
proc transpose data=ranges out=tranges(drop=_:) prefix=c;
    id days_range;
run;
*now merge them;
data cdataset;
    merge tranges tdaysrange;
run;


*transpose the daysrange dataset next by value;
proc transpose data=daysrange out=vdaysrange(drop=_:) prefix=v;
    id days_range;
   var value;
run;
*now transpose the table with all the categories;
proc transpose data=ranges out=vranges(drop=_:) prefix=v;
    id days_range;
run;
*now merge them;
data vdataset;
    merge vranges vdaysrange;
run;


*now join the the value and count datasets into one dataset!
data rowifieddaysrange;
    merge vdataset cdataset;
run;

And finally it's all in one row that looks something like this:

v1_9, c1_9, v10_19, c10_19, v20_29, c20_29, ...etc... v60_69, c60_69, ...etc

300, 6, 0, 0, 0, 0, ...etc... 250, 4, ...etc

That's a lot of work for something that aught to be straightforward!

SAS Macro Functions - how to return a value

I've replaced this article with a newer one: http://bioinfornetics.blogspot.com/2011/01/sas-macro-variables-type-casting.html

Please visit that link to find out how to return a value using the SAS macro programming language.

Btw, formats are hard to remember for SAS, but here's a very useful resource that lists them: http://www.sascommunity.org/wiki/TS_486_Functions,_Informats,_and_Formats

SAS Macro Programming is a pain in the butt!

I've been playing around with SAS macros these past few days and I must say I am totally shattered and depressed. It took me ages to find out why the SAS macros were not working in the first place - apparently macros are not meant to be run in "Open Space". They are meant to be placed inside data or proc steps... except that's not how you make global variables. However, it IS possible to run macros in open space though you need to add a function or two to get it working. Then I had issues with formatting and dates not being interpreted correctly. Finally I couldn't run a system command line command except on the local machine. Gah!

First. let's set some variables:

%let var_name = value;

The value can be a number or a string, but you're not meant to quote the string, like you usually do with SAS strings. If you want to see the value of var_name in the log (helps a great deal with debugging!):

%put &var_name.;

OK. Let's say you want to run a function to assign a value to a variable. Not straightforward - if you're in open space you need to call a special macro function %sysfunc:

%let var_name = %sysfunc( mdy(05, 18, 2010) ); * var_name holds date int value;

Note the command mdy() is in American Month-Day-Year mode.
What if we want to format this for output?

%let var_name = %sysfunc( putn( %sysfunc( mdy(05, 18, 2010) ), DATE7.) ); *holds 18MAY2010;

OK. Let's say I want to write a macro procedure/function or whatever they call it, and run it. Check out the silly if statements and loops.

%macro chk;
    * Check Sunday - yes, it counts the week days from Sunday - How annoying;
    * &yesterDate is already declared intnx(Day, "&sysdate"d, -1);
    %let weekday = %sysfunc( WEEKDAY( &yesterDate. ) );
    %if &weekday. = 1 %then %do;
        * intnx() is a great function - if its Sunday it sets yesterDate to Friday;
        %let yesterDate = %sysfunc( intnx(Day, &yesterDate., -2) );
    %end;
    * Now I want to declare an array... except macros don't do arrays, apparently!
    * Recommended guidlines say to do this:
    %let year = %sysfunc( YEAR(&yesterDate.) );

    %let Xmas1 = %sysfunc( mdy(12, 24, &year.) );
    %let Xmas2 = %sysfunc( mdy(12, 25, &year.) );
    %let Xmas3 = %sysfunc( mdy(12, 26, &year.) );
    %Do i = 1 %to 3;
        %if &yesterDate. = &&Xmas&i %then %do;
            %let yesterDate = %sysfunc( mdy(12, 23, &year.) );
            * Check its not Sunday again - Recursive Macro calling;
            %chk;
        %end;
    %end;
    * I have another loop to check for more holidays but I'm ommiting that;
%mend;
 
Now to run the macro I write:
 
%chk; 
 
but it would still work if I missed the ending semicolon - which is shocking because nothing else except multi-line comments work without an ending semicolon. Even single line comments and half finished statements need a semicolon on the end of a line.
 
OK. Now I had to run a command on the system. I had two options - the X command and the call system() command. X is a unixy command line thinga-ma-jig and can be called from anywhere - open space or not, while call system() can only be called inside a step. They are used like so:
 
X "move /path/to/file1.txt path/to/file2.txt"; *this is how you rename a file under unix/linux btw;
data _null_;
    call system("move /path/to/file1.txt /path/to/file2.txt"); *while windows does have the rename command, move works fine;
run;
 
An interesting thing is that you can send multiple commands through X seperated by semicolons while with call system() it does one command at a time.
 
Here's the problem... X didn't work on the remote server but call system() only worked on the local system. The rest of the script is meant to work on the remote server though. So this means that I'll need to run this part locally, comment it out temporarily, then run the whole script again remotely. Or I can skip that mess which took me ages to figure out why it wasn't working and manually change the file names.
 
SAS Sucks.