Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## 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)

5 REPLIES 5
Super User

## Re: Creating MTD Filter (SAS EG)

Wouldn't it just be:

``where t.order_dt < today()-1``
Obsidian | Level 7

## 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.
Super User

## 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)

Obsidian | Level 7

## Re: Creating MTD Filter (SAS EG)

Thank you!!
Lapis Lazuli | Level 10

## 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.
Discussion stats
• 5 replies
• 1676 views
• 2 likes
• 4 in conversation