Rolling dates using intnx

[hk24]

I'm trying to extract some data for the last 3 months on a rolling basis, which means that whenever the script is run, it would pull data for last 3 months based on the current month. I have two date columns, namely, campaign_date and year_month whose values are in the format of '03APR2023:00:00:00' and '2304' respectively and I'm using the column campaign_date for extracting the data, but the below script does not return any value. Can someone please review the code and let me know the modification required?

data newdata;
set libnamexyz.olddata (where=( CAMPAIGN_DATE between DATE() AND intnx('dtday',DATE(),-90)));
format cust_id 20.;
Run;

[SASKiwi · Accepted solution]

My guess is CAMPAIGN_DATE is a DATETIME and not a DATE variable. Try this:

data newdata;
set libnamexyz.olddata (where = ( CAMPAIGN_DATE between intnx('dtday',DATETIME(),-90) and DATETIME()));
format cust_id 20.;
Run;