Conditiona a SAS Macro base on names in a list

Question

new here.

I have written a pair of macros to search through one of our databases at work. This is an exemple of how I used it:

%LET SVlist=%str(R_PAR_SV_2012_VER3 R_PAR_SV);
%LET ovlist=%str(R_PAR_OV_2012_VER3 R_PAR_OV);




%LET FILTER=
(DIAGNOS like 'S72%' or diagnos contains ' S72' or
DIAGNOS like 'I21%' or diagnos contains ' I21'
);

%LET OVSELECT=PNR,
          PNRQ, 
          INDATUM, 
          OP, 
          DIAGNOS, 
          HDIA,
          alder,
          SJUKHUS;
%LET sVSELECT=&OVSELECT,UTDATUM,OPD1;

%macro ovsql(ovlist);
proc sql noprint;
%let n=%sysfunc(countw(&ovlist));
    %do i=1 %to &n;
        %let val = %scan(&ovlist,&i);


        create table work.&val as

            select &ovselect,
'SV' as souce
from r_par.&val
            where pnrq='0';


    %end;
quit;
%mend;

%macro svsql(svlist);
proc sql noprint;
%let n=%sysfunc(countw(&svlist));
    %do i=1 %to &n;
        %let val = %scan(&svlist,&i);


        create table work.&val as

            select &svselect,
'SV' as souce
from r_par.&val
            where pnrq='0';


    %end;
quit;
%mend;

%ovsql(&ovlist);
%svsql(&SVlist);

The above works fine:

As you can see, I bsically have two macro for dealing with 2 slightly different types of datasets. For this I use two lists:

What I am wondering is if there is a way to consolidate the macros so that you could condition the program to run the "OV-macro" when a set in the list contain the letter OV in the name and the SV when a set in the list contain the letters SV. Can this be done?

Here is my own attempt of which I have tried some variants, but not been able to get it to work, for reference.

options mprint;
%macro PSQL(list);
proc sql ;
%let n=%sysfunc(countw(&list));
    %do i=1 %to &n;
        %let val = %scan(&list,&i);
         %let g= 
%substr(&val,7,2);
         ;
        %put(&g);

         %if &g=:SV %then %do; 

        create table work._&val as

            select &svselect,
'SV' as souce
from r_par.&val
            where pnrq='0';
            %end;
            %else %if  &g=:OV %then %do ;
                    create table work._&val as

            select &ovselect,
'OV' as souce
from r_par.&val
            where pnrq='0';
    %end;
    %end;
quit;
%mend;

%PSQL(R_PAR_SV_2012_VER3 R_PAR_OV_2012_VER3);

I would really appriciate any help. I am a bit of a novice when it comes to the sas macro language. Thanks in advance

If you turn on OPTIONS MPRINT; what do you get in the log? Do you see any specific errors? — mjsqu

Joe Joe · Accepted Answer · 2014-03-14T14:00:05

I would write it a bit differently. Having two %if blocks doesn't really buy you all that much; it's still maintaining two bits of code, and the rest of the code isn't very interesting or difficult to maintain twice.

You could likely write this in one single piece of code:

create table work._&val as
        select &&shortval.select, "&shortval." as source
          from r_par.&val
          where pnrq='0';

Now add something to define &shortval as SV or OV, and you now don't need %if blocks at all. I think you use &g for this purpose, if that's its purpose, rename it to shortval (as g is a bad name for a macro variable) and go with

Further, some general commentary on the structure of code above:

In general, macro parsing this way is somewhat more difficult than other methods to achieve the same results. While it's possible to do so, it's more work, more error prone, and harder to maintain than data-driven macro calls.

Instead of having a macro that accepts a single argument containing multiple iterations' worth of data, write the macro as a single iteration macro, and call it multiple times programmatically. IE, have a dataset that contains one row each, the variable "Type":

R_PAR_SV_2012_VER3
R_PAR_OV_2012_VER3

And then do:

proc sql;
 select cats('%PSQL(',type,')') into :psqllist separated by ' '
        from have;
quit;

&psqllist.;

That allows you to more easily control the calls. It also allows you to more easily implement the above piece by easily including several macro variables.

What this means, is that if I wrote the above macro, I'd do it like this:

%macro psql(shortval=,val=);
  create table work._&val as
            select &&shortval.select, "&shortval." as source
              from r_par.&val
              where pnrq='0';
%mend psql;

proc sql;
 select cats('%PSQL(val=',type,',shortval=',substr(type,7,2),')') into :psqllist separated by ' '
        from have;
quit;
&psqllist.;

Now you have about ten lines of easily maintained code plus a small dataset, which you can even pull in from an excel sheet or other source of data (depending on where this list of values ought to come from).

Conditiona a SAS Macro base on names in a list

1 Answers