Improve speed for this script

[TienHan83]

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;

[Patrick]

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.

[Kurt_Bremser]

Why do you think you need those 101 separate datasets?

[Oligolas]

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;