BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
D-e-e
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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;

D-e-e
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

And my post remains the same.

D-e-e
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, a brief check of the documentation and:

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

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

Tom
Super User Tom
Super User

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)
D-e-e
Calcite | Level 5

And magic happened :))

 

Thank you very much!

Tom
Super User Tom
Super User

@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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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