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.
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