sqldatesasproc-sql

Getting different results due to date filtering method in SAS Proc SQL


I have a code chunk where I get different results based on how I filter for dates.

libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count 
from orac.table_A t
where blng_dt between '01-MAY-2023'd and '31-MAY-2023'd
);
quit;

The above code gives the expected 2+ million rows. However since I want the date filter to be dynamic I changed the code to the following.

libname orac oracle authdomain= XX path =XX schema = XX;
proc sql;
create table ship_bill_date as (
select distinct(t.shp_pro),min(t.blng_dt) as blng_dt,count(t.shp_pro) as count 
from orac.table_A t
where blng_dt between intnx('month', blng_dt, -2, 'b') and intnx('month', blng_dt, -2, 'e')
);
quit;

The above code chunk gives only 770 rows of output. How can I rectify this? The data type of blng_dt column is date.


Solution

  • If you want the month that is 2 months before TODAY() then just do that.

    where blng_dt between intnx('month',today(),-2) and intnx('month',today(),-2,'e')
    

    If PROC SQL is too dumb to understand the range is a constant and optimize the query properly then use macro code instead.

    where blng_dt between %sysfunc(intnx(month,%sysfunc(today()),-2))
                      and %sysfunc(intnx(month,%sysfunc(today()),-2,e))
    

    If you want the values that %SYSFUNC() generates to be readable by humans add the optional format specification and generate date literals instead of just literal number of days.

    10   %put
    11   where blng_dt between "%sysfunc(intnx(month,%sysfunc(today()),-2),date11)"d
    12   and "%sysfunc(intnx(month,%sysfunc(today()),-2,e),date11)"d
    13   ;
    where blng_dt between "01-MAY-2023"d and "31-MAY-2023"d