CODEWITHSUNDEEP
sasconditional-statementsexecution
Polbit
Polbit

Reputation: 3

SAS conditionally execute PROC SQL

I am building code that imports a daily file of sales from last 30 days, then replaces last 30 days of records in a main table. I want to only replace the last 30 days if the imported daily file has enough records:

proc sql;
select min(sale_date) into :oldestSale from daily_sales;
quit;

Here's a logic that I want to build in SAS:

IF oldestSale < (today() - 30) THEN PROC SQL to replace ELSE do nothing END

What would be the best solution? This is trivial in Python which I'm translating this from, but I'm stuck on SAS syntax for doing anything similar...

Upvotes: 0

Views: 1000

Answers (1)

Richard
Richard

Reputation: 27508

The 'logic' is quite vague in specifics, but the gist seems to be

  • import daily sales
  • presume daily sales is complete and every daily sale is represented
  • if daily sales info starting date is more than 30 days ago
    • remove all sales from main table from starting date
    • append current daily sales

One approach would be to have a macro that peforms the conditional remove/append as a 'replace' action. 

%macro process_daily_sales;
  proc import … out=daily_sales;
  run;

  %local oldestSale;
  %let oldestSale = %sysfunc(today()); %* sentinel value just in case;

  proc sql;
    select min(sale_date) into :oldestSale from daily_sales;
  quit;

  %local gap;
  %let gap = %eval ( %sysfunc(today()) - &oldestSale );

  %if &gap > 30 %then %do;
    proc sql;
      delete from main_sales where sales_date >= &oldestSale;
    quit;
    proc append base=main_sales data=daily_sales;
    run;
  %end;
%mend;

Upvotes: 2

Related Questions