topic Re: Creating MTD Filter (SAS EG) in SAS Enterprise Guide

Creating MTD Filter (SAS EG)

Tihannah — Tue, 22 Aug 2017 18:19:16 GMT

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

Re: Creating MTD Filter (SAS EG)

Reeza — Tue, 22 Aug 2017 18:36:10 GMT

Wouldn't it just be:

where t.order_dt < today()-1

Re: Creating MTD Filter (SAS EG)

Tihannah — Tue, 22 Aug 2017 19:17:46 GMT

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.

Re: Creating MTD Filter (SAS EG)

ballardw — Tue, 22 Aug 2017 21:19:35 GMT

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)

Re: Creating MTD Filter (SAS EG)

ShiroAmada — Wed, 23 Aug 2017 07:08:21 GMT

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

Try this one.

Re: Creating MTD Filter (SAS EG)

Tihannah — Wed, 23 Aug 2017 14:30:30 GMT

Thank you!!