## Macro iteration on two variables

Solved
Occasional Contributor
Posts: 8

# 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

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

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

## 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)
``````

All Replies
Super User
Posts: 9,866

## 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,935

## 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

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;

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
Posts: 9,866

## 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
Posts: 9,866

## 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
Posts: 8,289

## 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 )

Thank you very much!

Super User
Posts: 8,289

## 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;

...