topic Re: Optimizing Code/SAS Macro in SAS Programming

Optimizing Code/SAS Macro

Solly7 — Thu, 17 Feb 2022 09:03:23 GMT

Hi, I need help with the below,

my proc sql code will extract all the data from Transactions table and then the data step will filter for data processed only on 15FEB2022 if we run the code Today which is correct. So what I want is to get rid of data step and use only proc sql statement maybe with macro to achieve the results inside the proc sql code only, im not sure how I should do that.

The reason is because my source has extremely large data and when i query everything it takes longer to run, the idea is to filter the data from source(Transactions table) so that it extracts only whats required. See my code below

data transactions;
input process_date :yymmdd10. amount reference &:$50.;
format process_date date9.;
datalines;
2022-02-14 100 trn01
2022-02-14 200 trn02
2022-02-15 300 trn03
2022-02-15 500 trn05
2022-02-15 500 trn05
;
PROC SQL;
   CREATE TABLE trns AS 
   SELECT * from transactions
;QUIT;

DATA final_data;                                                     
    SET trns;                                                
  if weekday(today())in (2,3) then if process_date=today()-4;                   
  else if weekday(today()) in (5,6) then if process_date=today()-2;
  else if weekday(today()) = 4 then if today()-4<=process_date<=today()-2;
RUN;

Re: Optimizing Code/SAS Macro

Kurt_Bremser — Thu, 17 Feb 2022 09:42:55 GMT

Your first SQL does nothing except create an identical copy of the dataset, so discard it.

It's a larger WHERE clause in SQL:

where
  weekday(today()) in (2,3) and process_date = today() - 4
  or
  weekday(today()) in (5,6) and process_date = today() - 2
  or
  weekday(today()) = 4 and today() - 4 <= process_date <= today() - 2
  or
  weekday(today()) in (1,7)

The last condition is needed, as you do not have a subsetting IF for those weekdays in your data step. You may need to expand this condition with regards to process_date.

NEVER assume that your code will never be run on a weekend, it WILL happen one day. Believe me.

Re: Optimizing Code/SAS Macro

Solly7 — Thu, 17 Feb 2022 10:26:26 GMT

Hey @Kurt_Bremser , where would I be without you? Your solutions works like magic. I really appreciate it, God bless you.