SAS PROC SQL where between 4 mondays ago and last monday

Question

I am trying to sum up the past 4 weeks forecast vs sales data with each week starting on a monday.

For reference today is 8/6 so i want to start collecting their weekly sales and forecast for 7/5 going up until previous week monday, 7/26. I eventually plan to sum this 4wk forecast into one row using a do until last. statement grouping by Store and SKU to where i can use a put statement to make a new column to signal if they sold more than they forecasted or less. For instance lets pretend they had the below data

|Mon_DT            |STORE |SKU  |wk_FCST|wk_Sales|
|05July21:00:00:00 | 5    | abc | 10    | 12     |
|12July21:00:00:00 | 5    | abc | 10    | 16     |
|19July21:00:00:00 | 5    | abc | 10    |  7     |
|26July21:00:00:00 | 5    | abc | 10    | 12     |

with the do until last. the forecast will read as 40 and sales as 47 and ill say if sales < forecast then LOWforecast = 'Y';

However, I am having trouble just getting the between statement to work to pull only the last 4 weeks (Starting on monday).

DATA Getweeks; 
StartOversell = intnx('week.2',today(),-4);
endoversell = intnx('week.2',today(),-1);
format StartOversell yymmdd10.;
format endoversell yymmdd10.;
Run;
Proc sql;  
    connect to odbc (dsn='****' uid='****' pwd='***'); 
        create table work.Forecast1 as select distinct * from connection to odbc 
        (select MON_DT as DATE, Store_Number as Store, PROD_PKG_ID as SKU, FCST AS WK_FCST, SALES AS WK_sales, DIFF_QTY
            From FCST
                 where Mon_DT >= 'StartOversell'd and Mon_DT <= 'endoversell'd );
    disconnect from odbc;
quit;

I tried to use a macro variable as well but no luck.

You have to use macro variables. – Reeza Aug 06 '21 at 22:12 — Reeza, Aug 06 '21 at 22:12

Reeza · Accepted Answer · 2021-08-07T00:16:02.940

Use macro variables since the code uses explicit pass through and DB is expecting a database compliant date literal. All your SQL must be DB compliant in explicit pass through - not SAS SQL.

If you used MS SQL and it needs dates as "MM/DD/YY" for literals. So I will use the mmddyyS10. format which creates a macro variable that will look like that. You can convert the values using a put() function.

It's also a good idea to include the quotes in the macro variable in this case because Oracle needs single quotes, not double - not sure about MS SQL. The quote() function can be used to add quotes without issue.


    DATA Getweeks; 
    StartOversell = intnx('week.2',today(),-4);
    call symputx('startOversell', quote(put(startOverSell, MMDDYYS10.), "'"));
    ...
    Run;
    
    %put &startOversell;

Proc sql;  
    connect to odbc (dsn='****' uid='****' pwd='***'); 
        create table work.Forecast1 as select distinct * from connection to odbc 
        (select MON_DT as DATE, Store_Number as Store, PROD_PKG_ID as SKU, FCST AS WK_FCST, SALES AS WK_sales, DIFF_QTY
            From FCST
                 where Mon_DT >= &startOverSell and Mon_DT <= &endOverSell );
    disconnect from odbc;
quit;

Edit: you may want to consider what happens if you run it on a Monday and check if your dates align as expected.

Thank you! I really need to utilize the call symputx functions more. Also i changed it over to date9. since that is what the mon_dt is stored as. Lastly: No issues running it on monday, it goes back to the correct Monday. EDIT: Reason i had the format yymmdd10 is so i could make sure it was the correct monday. My fault for not mentioning that but it all worked out in the end — BluesGotTheCup, Aug 09 '21 at 18:28

SAS PROC SQL where between 4 mondays ago and last monday

1 Answers1