topic Re: Making a code dynamic in SAS Programming

Making a code dynamic

Kiteulf — Tue, 19 Mar 2019 09:29:39 GMT

Target3=(sum(1,M_201810)*sum(1,Target2))-1;
Target4=(sum(1,M_201809)*sum(1,Target3))-1;
Target5=(sum(1,M_201808)*sum(1,Target4))-1;

I have a code which calculates the Target1,Target2,Target3 etc...

this not dynamic

I have come so far on the dynamic code

%macro create(howmany);
   %do i=1  %to &howmany;

%let j =&i-1 ;

DATA _NULL_;
%let today=%sysfunc(today());
 %let somnow=%sysfunc(intnx(month,&today,-&i,b), yymmn6.);
%let somnow1=%sysfunc(intnx(month,&today,-&i,b), date9.);  
 run;

 %put &somnow;

DATA PD32T_&i;
set PD32TRANS;
format Date_&i yymmn6.;
Date_&i = "&somnow1"d;
Target&i=(sum(1,M_&somnow)*sum(1,Target&j))-1;
if Target&i = 0 then delete;
run;
Proc sql;
create table Target_data_&i as
select t0.objectregnbr 
,t1.Date&i as DATE
,t1.Target&i 
from PD32TRANS as t0
left join PD32T_&i as t1
on t0.objectregnbr = t1.objectregnbr
;
Quit;
 
 %end;
%mend create;

%create(12)

Need some help to complete the code. Maybe there are far better ways to do this without making 12 different files and aggregating them up... But principally looking for a way that calculates Target backwards in time

Re: Making a code dynamic

Kiteulf — Tue, 19 Mar 2019 09:30:44 GMT

The previous target (j) need to be calculated I guess

Re: Making a code dynamic

Astounding — Tue, 19 Mar 2019 10:49:33 GMT

This change is mandatory:

%let j = %eval(&i - 1);

Other changes are recommended, such as removing this line entirely:

DATA _NULL_;

%LET statements are never part of a DATA step.

Re: Making a code dynamic

andreas_lds — Tue, 19 Mar 2019 12:02:53 GMT

First thing to do: create a version without any macro-statement. If you already have that code, post it, so we can help. It seems as if the target-varialbes can be calculated using a normal do-loop and arrays. But to suggest something usefull i need to see data you have and what you expect as result.