Creating MTD Filter (SAS EG)

Solved
Occasional Contributor
Posts: 15

Creating MTD Filter (SAS EG)

Trying to create a month to date filter where all data is rolled up to the previous days date. Our data is updated hourly so it keeps pulling in the current days data, which I don't need.

The filter is written:

Case when YEAR(t1.Order_Dt)=YEAR(TODAY()-1) AND MONTH(t1.Order_Dt)=MONTH(TODAY()-1) then 'MTD' else 'NoMonth' end

It still keep pulling in data from the current business day. What am I doing wrong???

Accepted Solutions
Solution
‎08-23-2017 10:30 AM
Super User
Posts: 13,939

Re: Creating MTD Filter (SAS EG)

Look at something like

t1.order_dt between intnx('month',today()-1,0,'B') and (today()-1 )

You are getting todays date most of the time because month(today()-1) is month(today()) unless you are running on the first day of the month. You would also have to extend the the logic to day(order_dt) le day(today()-1)

All Replies
Super User
Posts: 23,998

Re: Creating MTD Filter (SAS EG)

Wouldn't it just be:

``where t.order_dt < today()-1``
Occasional Contributor
Posts: 15

Re: Creating MTD Filter (SAS EG)

I need to roll up everything within the current month. t1.order_dt < today()-1 when just give me all previos data going back 2 years.
Solution
‎08-23-2017 10:30 AM
Super User
Posts: 13,939

Re: Creating MTD Filter (SAS EG)

Look at something like

t1.order_dt between intnx('month',today()-1,0,'B') and (today()-1 )

You are getting todays date most of the time because month(today()-1) is month(today()) unless you are running on the first day of the month. You would also have to extend the the logic to day(order_dt) le day(today()-1)

Occasional Contributor
Posts: 15

Re: Creating MTD Filter (SAS EG)

Thank you!!
Frequent Contributor
Posts: 118

Re: Creating MTD Filter (SAS EG)

data SAMPLE(where=(dates<=intnx('days',today(),-1)));
format dates date9.;
do dates '01Jan2017'd to '31Dec2017'd;
output;
end;
run;

Try this one.
☑ This topic is solved.