topic Re: Loop\macro interaction in SAS Programming

Loop\macro interaction

hy2000 — Thu, 02 Jul 2020 03:04:57 GMT

Is there a way to reduce by one day on each loop\macro interaction?

I tried INTNX('day',TODAY(), -i,'s') but it seems to not accept "i" as one of the parameters instead of a number.

%macro test(X);

%do i = 1 %to 30;

PROC SQL;
   CREATE TABLE WORK.A AS
   SELECT t1.ID,
          t1.DATE_A
      FROM WORK.B t1
      WHERE t1.DATE_A <= INTNX('day',TODAY(),-i,'s');
QUIT;

/*CONTINUE HERE...*/

%end;

%mend test;

%test

Re: Loop\macro interaction

Reeza — Thu, 02 Jul 2020 03:13:49 GMT

WHERE t1.DATE_A <= INTNX('day',TODAY(),-&i,'s');

I think you just need to use the macro version of i, so &i.

I don't actually know where i is declared elsewhere in your code so no idea where its getting that value from.

Re: Loop\macro interaction

hy2000 — Thu, 02 Jul 2020 03:15:46 GMT

Is from here

%do i = 1 %to 30;

Re: Loop\macro interaction

Reeza — Thu, 02 Jul 2020 03:26:29 GMT

No. That would be &i not i.

Macro variables have a & in front of them, SQL or data step variables do not. So i is a data step or sql variable not your macro loop variable.

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Re: Loop\macro interaction

hy2000 — Sun, 05 Jul 2020 22:28:08 GMT

Thanks, Reeza this article helped me a lot, after reading it I actually achieve what I wanted, here’s the final code:

options nodate nonumber nocenter formdlim="-";
data a;
  input  date var_a;
format date ddmmyy10.;         
datalines;
22101 1
22101 1
22101 1
22101 1
22101 1
22100 1
22100 1
22100 1
22100 1
22100 1
22100 1
22099 1
22099 1
22099 1
22099 1
22099 1
22099 1
22099 1
;
run;
proc sql;
select date,
var_a
from work.a;
quit;
data b;
format date ddmmyy10.;
var_a = .;
run;
proc sql;
delete from b;
quit;
%let now = today();
%put &now;
%macro insert;
%do i = 1 %to 5;
	proc sql;
	insert into b
	select * from a
	where date < intnx('day', intnx('day', today(), -5), &i);
	quit;
%end;
%mend;
%insert