Loop to create multiple macro variables consisting of lists in SAS

Question

I am new to SAS macro writing and I have been struggling with writing a code for the following instance.

I have a list that is a combination of comp_id and date as follows -

DATA compno_date_list; 
  INPUT compno sdate; 
  DATALINES; 
12490 20090120 
87432 20090120
24643 20090120
87432 20090119
12490 20090105
24643 20090105
;

Now, this is a sample list. I need to run a huge macro on this list on a very large dataset. Since doing it for every row i.e. comp_ID - date combination will take a lot of time, I plan to select unique dates and create a macro variable that has all the dates. For each date in this macro variable, I need to create a macro variable that has the list of comp_IDs pertaining to that date. So, I plan to run the following macro that generates 'n' macro variables where 'n' is the total number of dates as follows -

%MACRO GET_MACRO_VARS();
%let j = 1;
%let sdate3 = %scan(&sdate1., &j.);
%do %while(&j.<= &ndate.);

proc sql;
  select distinct compno into : n&j.
  separated by ' '
 from compno_date_list where sdate=&sdate3.;
%let j= &j. + 1;
%let sdate3 = %scan(&sdate1., &j.);
%put &j.; 
%put &sdate3.;
%put &(n&j);

%end;
%mend;

%GET_MACRO_VARS();

Here, sdate1 is the macro variable containing all unique dates and ndate is a macro variable with the count of total unique dates.

But if I run this code, I get the following values in my log file from my last 3 %put statements in the macro above -

1 + 1
20090105
&(n1+ 1)

i.e. j is being treated as character variable(?). WHy is this happening and how do I rectify this? Thanks!

Please, please, do not learn to write macros this way. This is an example of a very bad programming style. You should almost never need to write a macro with no parameters; all of what you can do in that manner can be done without unnecessary macro loops. If you expound on why you want to do the above (ie, what you're going to do with these macro lists), I'd be happy to show you a proper way to do this. — Joe

Leo Leo · Accepted Answer · 2014-04-05T14:04:47

SAS recursively resolves everything that starts with a "%" or a "&". For a %let statement it stores everything to the right of the equal sign into the macro variable as a string (macro variables are always strings).

For "%let j= &j. + 1;" it does the following:

Resolve the "&j" so that you're left with "%let j= 1 + 1;";
Since SAS doesn't see anything starting with a % or a & to the right of the equal sign it sets j to "1 + 1".

In order to increment j you need to change the line to "%let j=%eval(&j + 1);". So the steps become the following:

Resolve the innermost "%" or "&" which is "&j", so the line becomes "%let j=%eval(1 + 1);".
Resolve the "%eval(1 + 1)" so the line becomes "%let j=2;".
Set j to "2".

Also the syntax for getting the value of a variable where the name is defined by another variable is &&n&j so &(n&j) wouldn't work.

Having said that, an easier way to do what you're trying to do is the following:

%do j=1 %to %sysfunc(countw(&sdate1));
  proc sql;
    select distinct compno into : n&j separated by ' '
    from compno_date_list where sdate=%scan(&sdate1, &j);
  quit;
  %put &&n&j;
%end;

One thing to note is if you're processing a large dataset you might want to try to avoid going over it for each date by either using multiple output data sets in a data step or using hash objects if possible since this seems like a very time consuming task.

Loop to create multiple macro variables consisting of lists in SAS

1 Answers