Desktop productivity for business analysts and programmers

Macro iteration on two variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Macro iteration on two variables

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.


Accepted Solutions
Solution
‎09-13-2017 10:33 AM
Super User
Super User
Posts: 7,860

Re: Macro iteration on two variables

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)

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: Macro iteration on two variables

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.

Super User
Posts: 6,543

Re: Macro iteration on two variables

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;

Occasional Contributor
Posts: 8

Re: Macro iteration on two variables

Posted in reply to Astounding

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.

Super User
Super User
Posts: 9,227

Re: Macro iteration on two variables

And my post remains the same.

Occasional Contributor
Posts: 8

Re: Macro iteration on two variables

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.

Super User
Super User
Posts: 9,227

Re: Macro iteration on two variables

Well, a brief check of the documentation and:

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

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

Solution
‎09-13-2017 10:33 AM
Super User
Super User
Posts: 7,860

Re: Macro iteration on two variables

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)
Occasional Contributor
Posts: 8

Re: Macro iteration on two variables

And magic happened Smiley Happy)

 

Thank you very much!

Super User
Super User
Posts: 7,860

Re: Macro iteration on two variables


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 455 views
  • 0 likes
  • 4 in conversation