Desktop productivity for business analysts and programmers

Creating MTD Filter (SAS EG)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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: 12,996

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)

View solution in original post


All Replies
Super User
Posts: 22,820

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: 12,996

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: 113

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 425 views
  • 2 likes
  • 4 in conversation