Tag Archive for macro

Creating waterfall plots in SAS using PROC GCHART

March 17, 2012 No comments

Recently, I needed to create a waterfall plot couldn’t use PROC SGPLOT since it was incompatible with publishing macros that use PROC GREPLAY on the platform that I was using; SGPLOT doesn’t generate plots in SAS catalogs but directly creates graphics files instead. Therefore, I decided that PROC GCHART needed to be given a go and it delivered what was needed .

The first step is to get the data into the required sort order:

proc sort data=temp;
by descending result;
run;

Then, it is time to add an ID variable for use in the plot’s X-axis (or midpoint axis in PROC GCHART) using an implied value retention to ensure that every record in the dataset had a unique identifier:

data temp;
set temp;
id+1;
run;

After that, axes have to be set up as needed. For instance, the X-axis (the axis2 statement below) needs to be just a line with no labels or tick marks on there and the Y-axis was fully set up with these, turning the label from vertical to horizontal as needed with the ANGLE option controlling the overall angle of the word(s) and the ROTATE option dealing with the letters, and a range declaration using the ORDER option.

axis1 label=none major=none minor=none value=none;
axis2 label=(rotate=0 angle=90 “Result”) order=(-50 to 80 by 10);

With the axis statements declared, the GCHART procedure can be defined. Of this, the VBAR statement is the engine of the plot creation with the ID variable used for the midpoint axis and the result variable used as the summary variable for the Y-axis. The DISCRETE keyword is needed to produce a bar for every value of the ID variable or GCHART will bundle them by default. Next, references for the above axis statements (MAXIS option for midpoint axis and AXIS option for Y-axis) are added and the plot definition is complete. One thing that has to be remembered is that GCHART uses run group processing so a QUIT statement is needed at the end to close it at execution time. This feature has its uses and appears in other procedures too though SAS procedures generally are concluded by a RUN statement.

proc gchart data=temp;
vbar id / sumvar=result discrete axis=axis2 maxis=axis1;
run;
quit;

Programming

, , , , , , , , , , , , , , , , , , ,

AND & OR, a cautionary tale

March 27, 2009 No comments

The inspiration for this post is a situation where having the string “OR” or “AND” as an input to a piece of SAS Macro code breaking a program that I had written. Here is a simplified example of what I was doing:

%macro test;
%let doms=GE GT NE LT LE AND OR;
%let lv_count=1;
%do %while (%scan(&doms,&lv_count,’ ‘) ne );
%put &lv_count;
%let lv_count=%eval(&lv_count+1);
%end
%mend test;

%test;

The loop proceeds well until the string “AND” is met and “OR” has the same effect. The result is the following message appears in the log:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:  %scan(&doms,&lv_count,’ ‘) ne
ERROR: The condition in the %DO %WHILE loop, , yielded an invalid or missing value, .  The macro will stop executing.
ERROR: The macro TEST will stop executing.

Both AND & OR (case doesn’t matter but I am sticking with upper case for sake of clarity) seem to be reserved words in a macro DO WHILE loop while equality mnemonics like GE cause no problem. Perhaps, the fact that and equality operator is already in the expression helps. Regardless, the fix is a simple one:

%macro test;
%let doms=GE GT NE LT LE AND OR;
%let lv_count=1;
%do %while (“%scan(&doms,&lv_count,’ ‘)” ne “”);
%put &lv_count;
%let lv_count=%eval(&lv_count+1);
%end
%mend test;

%test;

Now none of the strings extracted from the macro variable &DOMS will appear as bare words and confuse the SAS Macro processor but you do have to make sure that you are testing for the null string (“” or ”) or you’ll send your program into an infinite loop, always a potential problem with DO WHILE loops so they need to be used with care. All in all, an odd looking message gets an easy solution without recourse to macro quoting functions like %NRSTR or %SUPERQ.

Programming

, , , , , , , , ,

SAS Macro and Dataline/Cards Statements in Data Step

October 28, 2008 2 comments

I recently tried code like this in a SAS macro:

data sections;
        infile datalines dlm=”,”;
        input graph_table_number $15. text_line @1 @;
        datalines;
        “11.1           ,Section 11.1″,
        “11.2           ,Section 11.2″,
        “11.3           ,Section 11.3″
        ;
run;

While it works in its own right, including it as part of a macro yielded this type of result:

ERROR: The macro X generated CARDS (data lines) for the DATA step, which could cause incorrect results.  The DATA step and the macro will stop executing.

A bit of googling landed me on SAS-L where I spotted a solution like this one that didn’t involving throwing everything out:

filename temp temp;

data _null_;
        file temp;
        put;
run;

data sections;
        length graph_table_number $15 text_line $100;
        infile temp dlm=”,”;
        input @;
        do _infile_=
        “11.1           ,Section 11.1″,
        “11.2           ,Section 11.2″,
        “11.3           ,Section 11.3″
        ;
                input graph_table_number $15. text_line @1 @;
                output;
        end;
run;

filename temp clear;

The filename statement and ensuing data step creates a dummy file in the SAS work area that gets cleared at the end of every session. That seems to fool the macro engine into thinking that input is from a file and not the CARDS/DATALINES method to which it takes grave exception. The trailing @’s hold an input record for the execution of the next INPUT statement within the same iteration of the DATA step so that the automatic variable _infile_ can be fed as part of the input process in a do block with the output statement ensure that new records from the input buffer reach the data set being created.

This method does work but I would like to know the underlying reason as to why SAS Macro won’t play well with included data entry using DATALINES or CARDS statements in a data step, particularly when it allows other methods that using either SQL insert statements or standard variable assignment in data step. I find it such a curious behaviour that I remain on the lookout for the explanation why it is like this.

Programming

, , ,

Escaping brackets in SAS macro language

November 14, 2007 No comments

Rendering opening and closing brackets as pieces in SAS macro language programming caused me a bit of grief until I got it sorted a few months back. All of the usual suspects for macro quoting (or escaping in other computer languages) let me down: even the likes of %SUPERQ or %NRBQUOTE didn’t do the trick. The honours were left to %NRQUOTE(%(), which performed what was required very respectably indeed. The second "%" escapes the bracket for %NRQUOTE to do the rest.

Programming

, , ,

Restrictions on SAS libraries when macro catalogs are used

June 8, 2007 No comments

When you open up a SAS macro catalog so that its entries for use by other programs, it has a major impact on the ability to change the library reference used to access the catalog after it has apparently been unlocked.

options mstored sasmstore=bld_v001;

Using the line above will open the catalog for reading but there is no way to close it in order to change the library reference or deassign it until the SAS session is shut down. Even this line will not do the trick:

options nomstored sasmstore=”;

What it means in practice is that if you have a standard macro setting up access to a number of standard macro libraries, then that setup macro needs to check for any library references used and not try to reassign them, causing errors in the process.

Programming

, ,
 

  • As is commonly the case with places like these, all the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. With regards to any comments left on the site, I reserve the right to reject any that are inappropriate. Otherwise, whatever is said is the sole responsibility of whoever is leaving the comment.