SOAP0011 Some reasons why the SAS Macro Language is Useful

*** Soap Box On ***

Other interpretive languages, like PERL and APL have a 'macro'
capability, ie &&var = &value PERL constructs, ( as demonstrated in the UTLPDMO
PERL script on my site). APL can generate routines ( code ) and execute the
generated in stream with other APL code.

However, it is difficult for me to
see how PERL or APL  could 'easily' pass data, tokens and code fragments to and from
APL, SQL and PL/1 at compile and/or execution time.
In SAS, it is easy to pass data, code and tokens to/from SAS/IML (APL like language).
SAS Datstep (PL/1 like) and SAS-SQL ( Not to mention all other
SAS components). This communication can be done at compilation and often at
execution time.

It is easy to execute almost all SAS procedures and/or
SAS languages inside a 'macro' datastep.
Think of it as executing procedures, like SQL, IML, NLIN etc,
conditionally and iteratively inside a datastep.
This is much more powerful than the 'call execute' construct.
You can 'set tables', fetch observations ( with where clauses ), create macro
data vectors and then pass this data/code/tokens to procedures all inside
the macro language. This is especially useful in data warehousing.

I have picked the following examples because they are
often useful for  manipulating meta data in data
warehouseing. It is hard to come up with short examples.
I suspect most of these examples may fail ( syntax ) but
I know they can be done in SAS.


**** Untested Code off the top of my head ****

DATA X;
    S="SEL Y FROM X";
    Y=2;

PROC SQL;
      SELECT S INTO :C FROM X;
      CREATE TABLE Z AS &C;
QUIT;

Table Z has one column and one row, Column Y with value 2.
Note X could be an Oracle/Sybase/SqlServer/Access Table.

=============================================================

SELECT COUNT(DISTINCT X) INTO :XLEVELS; FROM TABLEA;%LET XLEFT=&XLEVELS;
SELECT DISTINCT(X) INTO :V1 - :V&XLEFT FORM TABLEA;

These two lines of code list all the distinct values of X,
and place those levels into macro variables V1-VNN.


============================================================


But the real power of the macro language is passing code/data
from one SAS language to another.

* SQL ;

PROC SQL;
SELECT COUNT(DISTINCT X) INTO :XLEVELS; FROM TABLEA;
SELECT DISTINCT(X) INTO :V1 - :V&XLEVELS FORM TABLEA;
QUIT;
END;

* PL/1 ;

DATA Z;

  ARRAY LEVELS{&xlevels} ( %do levels=1 %to &xlevels; &&V&levels %end; );
  ARRAY SQRTLEVELS{&xlevels};

  DO OVER ( LEVELS );

        IF LEVELS EQ &&V3 THEN
            SQRTLEVELS = SQRT(LEVELS);
        ELSE
            SQRTLEVELS = 1;
  END;

END;

* APL;

PROC IML;

     LEVELS {  %do levels=1 %TO &xlevels; &&V&levels %end; };
     PARTLEVELS=SHAPE({0},1,&XLEVELS);

     PARTLEVELS[,] = LEVELS[,] / LEVELS[,+]; /* not sure of syntax */

QUIT;
END;

==============================================================================

Here is an example using the macro compiler during datastep execution.

DATA X;

   Y='1+2+3+4+3*5';
   Z=RESOLVE('%eval('!!Y!!')');

   PUT Z=;

RUN;

The result is Z=25;

=============================================================================

A much more useful application would be to perform a specialised frequency analysis
on each column in a table. The frequency analysis is set up differently
for character and numeric variables and the data is combined into one
output table.

 For an example, see UTLFSEG, at my site.
Here we execute several procs while looping through all columns in a table.
Note any number of SAS languages or SAS procs could be inside the macro loop.
The macro provides data and code communications.
If I recall proc datasets, proc freq and poc append are inside the macro
loop.

=============================================================================

Classic 'macro' routine inside a datastep ( dumb example )

%macro area_circle(radius);

   3.14*&radius*&radius;

%mend circle;

data
      squares circles;

    set length;

    if  %area_circle(length) gt 10 then do;
        area = length*length;
        output squares;
    end;
    else do;
       area=%area_circle(length);
       output circles;
    end;

run;

=============================================================================

Tokens can very very useful in extending the flexibility
of your code.

Suppose you have designed a report for Coke Classic and now
management wants a similar report for Coke 2000.

You could just make another copy of Coke Classic and edit it.
However, you know that this will create extra work in the
months to come, because common future changes will have to be
maintained in two places. A better solution is to use the
macro language and create two tokens, Coke_Classic and Coke_2000.
Wrap a macro around your code and call the code twice once for
Coke_Classic and once for Coke_2000.


===============================================

Another example using the macro datastep execution.

Fragments from a Post by Ian Whitlock

data table1 ;
   input id age sex $ cond $char50. ;
cards ;
1 21 M age > 12 and age < 26
2 63 F age < 18
3 45 M age > 18 and sex = M
;
run;

data _null_;
   length flag $ 1 ;

   set table1;

   cond = tranwrd ( cond , 'age' , '&age' ) ;
   cond = tranwrd ( cond , 'sex' , '&sex' ) ;
   call symput ( 'age' , put ( age , 3. ) ) ;
   call symput ( 'sex' , sex ) ;
   flag = resolve ( '%eval ( ' || cond || ')' ) ;

   put flag=;

run;

output
  FLAG=1
  FLAG=0
  FLAG=1

=====================================================

Yet another example

proc format;

   value $fun

     'WBC' = '1*&base + 2*&base*&base'
     'HGB' = '2*&base + 3*&base*&base'
     'DAI' = '3*&base + 4*&base*&base'
     'RGB' = '4*&base + 5*&base*&base'
;
run;

data x;

 input test $ base;

 call symput('base',base);

 count = resolve ( '%eval('!!put ( test, $fun. )!!')' );

 put count=;

cards;
WBC 55
WBC 77
DAI 89
RGB 12
RGB 45
HGB 11
;;;;

run;

===============================================================