topic Re: Data driven macro in SAS Programming

Data driven macro

japfvg — Thu, 10 Dec 2020 08:45:55 GMT

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

Re: Data driven macro

Kurt_Bremser — Thu, 10 Dec 2020 09:44:31 GMT

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;

Re: Data driven macro

ChrisNZ — Thu, 10 Dec 2020 09:47:19 GMT

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;

Re: Data driven macro

Shmuel — Thu, 10 Dec 2020 09:47:45 GMT

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.

Re: Data driven macro

japfvg — Thu, 10 Dec 2020 15:01:59 GMT

Thanks a lot for your quick answers!!!

I'll try with call_execute command.

Have a great day everyone!!

Fer