topic Macro iteration on two variables in SAS Enterprise Guide

Macro iteration on two variables

D-e-e — Thu, 07 Sep 2017 09:54:20 GMT

I have defined two macro variables, both of them have 5 values, e.g. %month = 5 4 3 2 1 and %year = 20 19 18 17 16.

In a proc sql I have selected variables which must have values as stated above, e.g.

%macro my_table (month, year);

proc sql;

create table XXX as

select ...

where t1.mnt = &month and t1.yr = &year;

quit;

%mend my_table;

Now I need to define macro which will iterate this process and create 5 tables. In every table I need to have pairs: Mnt=5 & Yr=20, Mnt=4 & Yr=19, etc. This is how I have written macro:

%macro preparation_table;
   %let k=1;
    %let t=1;
   %let ti=%scan(&month,&k);
    %let pi=%scan(&year,&t);

   %do %while (&ti = 5);
       %my_table(&ti, &pi, &k);
       %let k=%eval(&k+1);
        %let t=%eval(&t+1);
       %let ti=%scan(&month,&k);
        %let pi=%scan(&year,&t);
   %end;
%mend preparation_table;

%preparation_table;

In log I get message

%LET _CLIENTTASKLABEL=;
96         %LET _CLIENTPROJECTPATH=;
97         %LET _CLIENTPROJECTNAME=;
98         %LET _SASPROGRAMFILE=;
99
100        ;*';*";*/;quit;run;
101        ODS _ALL_ CLOSE;
102
103
104        QUIT; RUN;
105

with no result, i.e. no tables created. Please help.

Re: Macro iteration on two variables

RW9 — Thu, 07 Sep 2017 10:33:50 GMT

We need more information. Put in your code before you run anything, the debug options so you can see what is going on:

options mlogic mprint symbolgen source source2;

As always, it is really not a good idea to spit data up into separate datasets. All you are doing is creating work for yourself, as after that point you then have to create loop code each time you want to do anything. Far better to use by group processing.

If you still go down the route, then:

data _null_;
  do year=16 to 20;
    do month=1 to 5;
      yr=put(year,2.);
      mt=put(month,2.);
      call execute(cats('data want',yr,mt,'; set have (where=(mnt=',mt,' and yr=',yr,')); run;');
    end;
  end;
run;

Is far simpler.

Re: Macro iteration on two variables

Astounding — Thu, 07 Sep 2017 11:17:42 GMT

Possibly you are getting the error for specifying 3 parameters when calling %MY_TABLE since the macro definition only allows for 2 parameters. (I guess it's possible that this isn't really the issue and that you simplified the post, and that &K is needed to name the output table from SQL.)

While it's true that we need more info to see why you got no tables, your original logic would only create one table at most. Consider your loops:

%do %while (&ti = 5);
       %my_table(&ti, &pi, &k);
       %let k=%eval(&k+1);
        %let t=%eval(&t+1);
       %let ti=%scan(&month,&k);
        %let pi=%scan(&year,&t);
   %end;

After one iteration, &TI is no longer equal to 5, so the loop is over. A much simpler logic is possible, which also replaces the assignments that appear before the loop:

%do k=1 %to 5;

%let ti = %scan(&month, &k);

%let pi = %scan(&year, &k);

%my_table (&ti, &pi)

%end;

Re: Macro iteration on two variables

D-e-e — Wed, 13 Sep 2017 12:40:54 GMT

This is what I have right now:

%let month=199 187 175 163 151;
%let year=16 15 14 13 12;

%macro my_table(month, year);

   proc sql;
       create table WANT as
           select t2.var1,
               t2.var2,
               t2.var3
           from TABLE2 t2 inner join TABLE1 t1 on ((input(t2.var3,z10.0)) = t1.var3)
               where t2.mnth = &month and t2.var3 ne 0;
   quit;

   proc sql;
       create table WANT_&year as
           select t1.var1,
               t1.var2,
               t1.var3,
           (intck('year',(datepart(t2.var4)), (datepart(t2.var5)), 'c')) as yrs_&year,
           calculated yrs_&year + (intck('month',intnx('year', datepart(t2.var6), calculated yrs_&year, 's'), (datepart(t2.var7)), 'c')/12) as ttm_&year
       from work.TABLE4 t1 inner join work.TABLE3 t2 on (t1.var8=t2.var8 and t1.var9=t2.var9);
   quit;

%mend my_table;

%macro preparation_table;
   %let k=1;
    %let t=1;
   %let ti=%scan(&month,&k);
    %let pi=%scan(&year,&t);

   %do %while (&ti < 5);
       %my_table(&ti, &pi);
       %let k=%eval(&k+1);
        %let t=%eval(&t+1);
       %let ti=%scan(&month,&k);
        %let pi=%scan(&year,&t);
   %end;
%mend preparation_table;

%preparation_table;

With added

options mlogic mprint symbolgen source source2;

I get following:

MLOGIC(_EG_RESTORENOTESANDSOURCE): Beginning execution.
MLOGIC(_EG_RESTORENOTESANDSOURCE): %GLOBAL _EGNOTES
MLOGIC(_EG_RESTORENOTESANDSOURCE): %GLOBAL _EGSOURCE
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
MLOGIC(_EG_RESTORENOTESANDSOURCE): Ending execution.
118
119
120        %_eg_hidenotesandsource;
MLOGIC(_EG_HIDENOTESANDSOURCE): Beginning execution.
MLOGIC(_EG_HIDENOTESANDSOURCE): %GLOBAL _EGNOTES
MLOGIC(_EG_HIDENOTESANDSOURCE): %GLOBAL _EGSOURCE
MLOGIC(_EG_HIDENOTESANDSOURCE): %LET (variable name is _EGNOTES)
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MLOGIC(_EG_HIDENOTESANDSOURCE): %LET (variable name is _EGSOURCE)
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MLOGIC(_EG_HIDENOTESANDSOURCE): Ending execution.
MLOGIC(_EG_RESTORENOTESANDSOURCE): Beginning execution.
MLOGIC(_EG_RESTORENOTESANDSOURCE): %GLOBAL _EGNOTES
MLOGIC(_EG_RESTORENOTESANDSOURCE): %GLOBAL _EGSOURCE
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
MLOGIC(_EG_RESTORENOTESANDSOURCE): Ending execution.

Re: Macro iteration on two variables

RW9 — Wed, 13 Sep 2017 12:43:52 GMT

And my post remains the same.

Re: Macro iteration on two variables

D-e-e — Wed, 13 Sep 2017 12:45:59 GMT

As you can see I cannot use

do month=1 to 5

because I don't have months listed like 1, 2, 3, 4, 5.

Re: Macro iteration on two variables

RW9 — Wed, 13 Sep 2017 12:59:55 GMT

Well, a brief check of the documentation and:

do month=199,187,175,16,151;

do year=16,15,14,13,12;

Re: Macro iteration on two variables

Tom — Wed, 13 Sep 2017 13:14:09 GMT

Don't make life harder by coding your own increments for an iterative loop counter.

Just use an iterative %DO loop do and SAS will automatically increment the index variable for you.

%let monthlist=199 187 175 163 151;
%let yearlist=16 15 14 13 12;
%do item=1 to %sysfunc(countw(&monthlist));
   %let month=%scan(&monthlist,&item);
   %let year=%scan(&yearlist,&item);
   ... code that references &YEAR and &MONTH
%end;

So let's recode your inner macro a little. Mainly let's change the output table names so that they are unique. But we can also eliminate a lot of the extra complexity by using the DTYEAR and DTMONTH intervals instead of having to convert your datetime values to dates.

%macro my_table(month, year);

proc sql noprint;
create table WANT_MONTH_&month as
select t2.var1
     , t2.var2
     , t2.var3
from TABLE2 t2 
inner join TABLE1 t1 
on input(t2.var3,10.) = t1.var3
where t2.mnth = &month and t2.var3 ne 0
;

create table WANT_YEAR_&year as
select t1.var1
     , t1.var2
     , t1.var3
     , intck('dtyear',t2.var4,t2.var5,'c') as yrs_&year
     , calculated yrs_&year 
       + intck('dtmonth',intnx('dtyear',t2.var6,calculated yrs_&year,'s'),t2.var7,'c')/12 
      as ttm_&year
from work.TABLE4 t1 
inner join work.TABLE3 t2 
on t1.var8=t2.var8 and t1.var9=t2.var9
;

quit;

%mend my_table;

So now your outer macro can look like this

%macro preparation_table(monthlist,yearlist);
%local i ;
%do i=1 %to %sysfunc(countw(&monthlist));
  %my_table(month=%scan(&monthlist,&i),year=%scan(&yearlist,&i))
%end;
%mend preparation_table;

So you can now call it like this:

%preparation_table(monthlist=199 187 175 163 151,yearlist=16 15 14 13 12)

Or if you prefer you can reference your macro varaibles.

%let month=199 187 175 163 151;
%let year=16 15 14 13 12;
%preparation_table(monthlist=&month,yearlist=&year)

Re: Macro iteration on two variables

Tom — Wed, 13 Sep 2017 13:31:47 GMT

@D-e-e wrote:

I have defined two macro variables, both of them have 5 values, e.g. %month = 5 4 3 2 1 and %year = 20 19 18 17 16.

...

Now I need to define macro which will iterate this process and create 5 tables. In every table I need to have pairs: Mnt=5 & Yr=20, Mnt=4 & Yr=19, etc. This is how I have written macro:

%macro preparation_table;
   %let k=1;
    %let t=1;
   %let ti=%scan(&month,&k);
    %let pi=%scan(&year,&t);

   %do %while (&ti = 5);
       %my_table(&ti, &pi, &k);
       %let k=%eval(&k+1);
        %let t=%eval(&t+1);
       %let ti=%scan(&month,&k);
        %let pi=%scan(&year,&t);
   %end;
%mend preparation_table;

...
with no result, i.e. no tables created. Please help.

Your inner %DO %WHILE() condition looks wrong. You are testing the value of the variable that has the month number, not the variable that has the index into the list of months. So if the first month in the list of months is not exactly 5 then that loop never runs. And if it is 5 but the next one isn't then it runs only once.

Re: Macro iteration on two variables

D-e-e — Wed, 13 Sep 2017 14:33:55 GMT

And magic happened :))

Thank you very much!