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;
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?
@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, ');'));
hi
i have given sample data,macro code in the attachement,kindly have a look.
%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".
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)
@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?
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;
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;
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.