Hi all, I have this project in mind but not sure how to complete it...
I have an "input table" like this:
account_number | Date1 | Date2 |
1 | 01/dec/2020 | 02/dec/2020 |
2 | 02/dec/2020 | 03/dec/2020 |
I need to create a macro which iterates every record from the "input table" and use every value from every field as a filter in two proc sql, the code I had is this:
%macro queries(ctagl, fecq1, fecspro);
proc sql;
select * from table1
where eoddate = '&fecq1' and acctfrom = '&ctagl';
quit;
proc sql;
select * from table2
where anio = year(&fecspro) and mes = month(&fecspro) and dia = day(&fecspro)
and ctagl = &ctagl;
quit;
%mend;
%macro execute_queries
%do %until eof(input_table);
%macro queries(account_number, Date1, Date2);
%end;
%mend;
I'm not sure if %do %until works with eof (I had used before without the macro) or is another way to go through input_table using macro code?
Any help would be greatly appreciated.
Thanks a lot!!
Fernando
First, correct your macro:
%macro queries(ctagl, fecq1, fecspro);
proc sql;
select * from table1
where eoddate = &fecq1 and acctfrom = &ctagl;
/* single quotes prevent resolution of macro triggers */
/* if acctfrom is character, add double quotes around &ctagl */
quit;
proc sql;
select * from table2
where anio = year(&fecspro) and mes = month(&fecspro) and dia = day(&fecspro)
and ctagl = &ctagl;
quit;
%mend;
Then, to call your macro from the input_table, use CALL EXECUTE in a data step:
data _null_;
set input_table;
call execute(cats('%nrstr(%queries(',account_number,',',date1,',',date2,'))'));
run;
Or write the calls to a temporary file and include that:
filename inc_file temp;
data _null_;
set input_table;
file inc_file;
string = cats('%queries(',account_number,',',date1,',',date2,')');
put string;
run;
%include inc_file;
filename inc_file;
First, correct your macro:
%macro queries(ctagl, fecq1, fecspro);
proc sql;
select * from table1
where eoddate = &fecq1 and acctfrom = &ctagl;
/* single quotes prevent resolution of macro triggers */
/* if acctfrom is character, add double quotes around &ctagl */
quit;
proc sql;
select * from table2
where anio = year(&fecspro) and mes = month(&fecspro) and dia = day(&fecspro)
and ctagl = &ctagl;
quit;
%mend;
Then, to call your macro from the input_table, use CALL EXECUTE in a data step:
data _null_;
set input_table;
call execute(cats('%nrstr(%queries(',account_number,',',date1,',',date2,'))'));
run;
Or write the calls to a temporary file and include that:
filename inc_file temp;
data _null_;
set input_table;
file inc_file;
string = cats('%queries(',account_number,',',date1,',',date2,')');
put string;
run;
%include inc_file;
filename inc_file;
No need for a macro. Try something like this (untested as no data was provided):
data _null_;
set TABLE;
call execute( 'proc sql;'
||'select * from TABLE '
||'where EODDATE =' || DATE1
||' and ACCTFROM =' || quote(trim(ACCOUNT_NUMBER))
||'; quit;' );
run;
1) To execute a macro use (in your case) %queries and not "%macro queries(...)
2) You don't need the second macro "execute_queries" as it can be done in a data step
using call execute() function:
data <output>;
set input_table;
cmd = catx("," , "%queries(", account_number,Date1,Date2, ")");
call execute(cmd);
run;
3) Running "proc sql; select ..." will write the output to the "output" window or "result" tab,
depending on sas platform you use.
You should define what kind f final output you want. either a dataset or a report.
Thanks a lot for your quick answers!!!
I'll try with call_execute command.
Have a great day everyone!!
Fer
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.