I have script as below but it is running pretty slow... any way to make it run faster?
data _null_;
format a date9.;
do i=1 to 101;
a=intnx('month',"31jan2014"d,i,'e');
call symput(compress("dt"||i),a);
call symput('cnt',i);
end;
run;
%macro x;
%do i=1 %to &cnt;
%let y=%sysfunc(putn(&&dt&i,date9.));
data a_&y;
set txn.append_txn;
if report_date=&&dt&i then
output;
run;
%end;
%mend;
%x;
You could re-write your macro as below so it doesn't read the source table 101 times.
There would be other improvements possible but I've taken a "least change" approach to the code you've shared.
libname txn "%sysfunc(pathname(work))";
data txn.append_txn;
do report_date="31jan2014"d,"30jun2020"d;
output;
end;
run;
data _null_;
format a date9.;
do i=1 to 101;
a=intnx('month',"31jan2014"d,i,'e');
call symput(compress("dt"||i),a);
call symput('cnt',i);
end;
run;
%macro x();
data
%do i=1 %to &cnt;
%let y=%sysfunc(putn(&&dt&i,date9.));
a_&y
%end;
%str(;)
set txn.append_txn;
%do i=1 %to &cnt;
%let y=%sysfunc(putn(&&dt&i,date9.));
if report_date=&&dt&i then output a_&y;
%if &i ne &cnt %then %str(else);
%end;
run;
%mend;
%x();
If you really need to create these monthly tables then I'd suggest that you use a naming pattern <base_name>_<yyyymmdd> with the date being beginning of the month.
Why do you think you need those 101 separate datasets?
Do you want to limit the observations in append_txn to the results that lay in a 101 days time range?
%let startDate="31jan2014"d;
data want;
set txn.append_txn;
where &startDate.<=report_date<=intnx('day',&startDate.,101);
run;
- Cheers -
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.