Creating MTD Filter (SAS EG)

[Tihannah]

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

[Reeza]

Wouldn't it just be:

where t.order_dt < today()-1

[Tihannah]

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.

[ballardw · Accepted solution]

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)

[Tihannah]

Thank you!!

[ShiroAmada]

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

Try this one.