topic Re: How to invoke a macro inside a do loop in SAS Programming

How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 05:17:50 GMT

Iam in process of automating SAS code,I have a macro whose input argument is date.
For example:

%monthly("20jun2019"d)
%monthly("21jun2019"d)
.
.
.
%monthly("29jun2019"d)

I need to automate the above code.i have tried using do loop but it didn't worked.here is the code that I have tried.

Data d;
Do i="20jun2019"d to "29jun2019"d;
Output
Call execute ("%monthly",i);
End;
Run;

Kindly let me know how to invoke macro inside do loop or is there any other process to automate the above process.

Re: How to invoke a macro inside a do loop

koyelghosh — Tue, 25 Jun 2019 05:32:22 GMT

I am not aware of the exact error you are getting and also I am not aware of the macro code but I think you are not passing i as a parameter to monthly macro. The monthly macro is expecting a parameter perhaps.

Did you try CALL EXECUTE ('%monhtly(i)') instead of what you have written?

Re: How to invoke a macro inside a do loop

andreas_lds — Tue, 25 Jun 2019 05:45:36 GMT

@sravanece11 wrote:
Iam in process of automating SAS code,I have a macro whose input argument is date.
For example:

%monthly("20jun2019"d)
%monthly("21jun2019"d)
.
.
.
%monthly("29jun2019"d)

I need to automate the above code.i have tried using do loop but it didn't worked.here is the code that I have tried.

Data d;
Do i="20jun2019"d to "29jun2019"d;
Output
Call execute ("%monthly",i);
End;
Run;

Kindly let me know how to invoke macro inside do loop or is there any other process to automate the above process.

You need to pass a valid macro-call to call execute, also note that the behaviour of call execute depends on the quotes you use, see docs for details.

This should work as expected:

call execute(cats('%monthly(', i, ');'));

Re: How to invoke a macro inside a do loop

Kurt_Bremser — Tue, 25 Jun 2019 06:10:12 GMT

And if you have macro code inside %monthly, you need to further delay the macro resolution:

call execute(cats('%nrstr(%monthly(', i, '));'));

It is always a good idea to use %nrstr when calling macros with call execute.

Re: How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 08:47:59 GMT

Hi

After running the code that you suggested,iam getting output of only the
last date ie 27jun2019.

I need output for all the dates provided in the do loop

Re: How to invoke a macro inside a do loop

Kurt_Bremser — Tue, 25 Jun 2019 08:50:01 GMT

Please show the code of your macro.

Re: How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 09:00:41 GMT

i have given sample data,macro code in the attachement,kindly have a look.

Re: How to invoke a macro inside a do loop

Kurt_Bremser — Tue, 25 Jun 2019 09:08:31 GMT

%macro download(daty);
proc sql;
create table asp as select a.name,a.income,
             b.name as name1,b.income as income1,
             c.name as name2,c.income as income2,
             a.dat from
        precalltesttab_61 a,
        precalltesttab_62 b,
        precalltesttab_63 c
        where a.dat=b.dat
        and b.dat=c.dat
        and a.dat=c.dat
        and a.dat=&daty.
        ;
        quit;
%mend download;

You overwrite dataset asp with every macro call, so only the result of the last call will "survive".

Re: How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 09:12:48 GMT

so what changes should i make in the code such that i should be able to retrive data for all the macros invoked in do loop.( i mean for all the dates provided in do loop)

Re: How to invoke a macro inside a do loop

andreas_lds — Tue, 25 Jun 2019 09:29:39 GMT

@sravanece11 wrote:

so what changes should i make in the code such that i should be able to retrive data for all the macros invoked in do loop.( i mean for all the dates provided in do loop)

Depends on what you need: one dataset for each date (not recommended, because further processing will require more loops) or one dataset with the data of all dates you currently pass a parameter?

Re: How to invoke a macro inside a do loop

Kurt_Bremser — Tue, 25 Jun 2019 09:34:11 GMT

Add a timestamp to the dataset name:

%macro download(daty);
%local datestring;
%let datestring=%sysfunc(putn(&daty.,yymmddn8.));
proc sql;
create table asp&datestring. as select a.name,a.income,
             b.name as name1,b.income as income1,
             c.name as name2,c.income as income2,
             a.dat from
        precalltesttab_61 a,
        precalltesttab_62 b,
        precalltesttab_63 c
        where a.dat=b.dat
        and b.dat=c.dat
        and a.dat=c.dat
        and a.dat=&daty.
        ;
        quit;
%mend download;

data _null_;
do i = '20jun2019'd to '27jun2019'd;
  call execute('%nrstr(%download('!!put(i,best.)!!'));');
end;
run;

But as @andreas_lds already mentioned, this is usually not a good idea.

Re: How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 10:18:59 GMT

one dataset with the data of all dates i currently pass as a parameter

Re: How to invoke a macro inside a do loop

andreas_lds — Tue, 25 Jun 2019 11:29:20 GMT

Untested code, you don't need a loop any more, just pass first and last date to the macro.

%macro download(fromDate, untilDate);
proc sql;
create table asp as select a.name,a.income,
             b.name as name1,b.income as income1,
             c.name as name2,c.income as income2,
             a.dat from
        precalltesttab_61 a,
        precalltesttab_62 b,
        precalltesttab_63 c
        where a.dat=b.dat
        and b.dat=c.dat
        and a.dat=c.dat
        and (&fromDate. <= a.dat <= &untilDate.)
        ;
        quit;
%mend download;

Re: How to invoke a macro inside a do loop

Kurt_Bremser — Tue, 25 Jun 2019 12:01:13 GMT

Create intermediate datasets and append:

%macro download(daty);

proc sql;
create table int as
select
  a.name,
  a.income,
  b.name as name1,
  b.income as income1,
  c.name as name2,
  c.income as income2,
  a.dat
from
  precalltesttab_61 a,
  precalltesttab_62 b,
  precalltesttab_63 c
where
  a.dat=b.dat
  and b.dat=c.dat
  and a.dat=&daty.
;
quit;

proc append data=int base=asp force;
run;

%mend download;

%if %sysfunc(exist(asp)) %then %do;
proc delete data=asp;
run;
%end;

data _null_;
do i = '20jun2019'd to '27jun2019'd;
  call execute('%nrstr(%download('!!put(i,best.)!!'));');
end;
run;

Re: How to invoke a macro inside a do loop

sravanece11 — Tue, 25 Jun 2019 12:18:59 GMT

Thanks a lot,it's working.