Blogs

Blogs

Improving on a SAS programming pattern

9
By Topics | Programming Tips

Earlier this week I described a common programming pattern in the SAS macro language. The pattern sets up a loop for processing each distinct value of a classification variable. The program uses the PROC SQL SELECT INTO feature to populate SAS macro variables. The effect: you can roll your own BY processing in SAS.

I posted the example and received many useful comments, including news about new features in SAS 9.3 that allow us to improve this pattern. Instead of two SELECT statements (one to gather a count, and a second to populate a range of variables), we can now achieve the same with just one SELECT statement (and thus, just one pass through the data):

/* SAS 9.3 approach */
/* Create macro vars with values and a total count of distinct values */
proc sql noprint;
  select distinct TYPE into :varVal1- from SASHELP.CARS;
  %let varCount = &SQLOBS.;
quit;

With the new SAS 9.3 syntax, we can now specify an open-ended range for macro variable names. SAS will allocate as many macro variables as are needed to store each value. The values will be TRIMMED of whitespace by default, but you can control this by specifying NOTRIM before the FROM keyword.

Since we don't need to know the count ahead of time, we can then safely use &SQLOBS after the SELECT to determine the upper bound of our index.

You can also specify leading zeros in the macro variable range, which can help with sorting the ordinals later. Here's a revised example:

proc sql noprint;
  /* using leading zero */
  select distinct MAKE into :varVal01- from SASHELP.CARS;
  %let varCount = &SQLOBS.;
quit;

The accompanying image shows the resulting macro variables in the SAS Macro Variable Viewer, neatly collated in numerical order. (Remember that if you do add the leading zeros, you may need to use the Zw.d format when building up the macro variable names within a loop.)

Tags macro programming sas 9.3 SAS programming sql
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

9 Comments

  1. Paul Homes on

    Very nice! I know I'll be using this alot. Thanks for sharing it.

    I think an additional small but valuable improvement, for a future SAS version, could be to follow a similar pattern to that used in SAS/IntrNet and SAS Stored Processes where the macro 'array' has a 0 indexed item containing the count of members (i.e. varVal0). It would save the extra statement with SQLOBS which I know I'll always be doing.

    • Peter Lancashire on

      Yes, that seems nice but it really is a kludge. With a background in C I know I would trip over this as element 0 of an array is the first element. I actually found a bug in SAS a while ago where starting at zero failed in ODS MATCH_ALL. Please keep the array pure.

      A far better improvement would be to give the macro language a genuine array type. Then you would have a function or property to return the number of elements. A great deal of messing about with the macro language is just to work around this lack. Why not make the macro facilities match the table-oriented paradigm of the dataset?

      • Chris Hemedinger
        Chris Hemedinger on

        Peter,

        An array purist, eh? That's what happens when your pedigree is C programming and not Visual Basic. Of course you can get the 0-based behavior in this SAS macro example:

        proc sql;
  select distinct make into :make0- from sashelp.cars;
quit;

        The SAS macro language is more akin to a preprocessor than a compiled programming language, since its main use is to generate text which will eventually be interpreted into a SAS program. The SAS language continues to evolve, and many of the traditional tasks for which we rely on SAS macro can sometimes be better expressed in new constructs, such as defining your own DATA step functions with FCMP.

        • Dave Garbutt on

          Well I also vote for better structures in the macro lang - or moere call exec() functionality in the datastep.

          I propose freeing the hash object from the datastep so it can be available in in a macro too.
          As option it should be allowed to use either the DDV or the PDV when finding etc.

  2. Pingback: Implement BY processing for your entire SAS program - The SAS Dummy

Back to Top