topic Re: Improve speed for this script in SAS Programming

Improve speed for this script

TienHan83 — Wed, 13 Sep 2023 05:40:34 GMT

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;

Re: Improve speed for this script

Patrick — Wed, 13 Sep 2023 06:29:59 GMT

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.

Re: Improve speed for this script

Kurt_Bremser — Wed, 13 Sep 2023 06:35:09 GMT

Why do you think you need those 101 separate datasets?

Re: Improve speed for this script

Oligolas — Wed, 13 Sep 2023 07:33:36 GMT

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;