Hi, I
I want to exclude last month from a dynamic date (The time frame keeps changing) e.g. if I select quarter 1 (i want to exclude March data) if I select a year from Jan to Dec - I want to exclude Dec data, or if I select data from Feb to May I want to exclude May from the results so exclude last month every time a user selects a time frame.
Have:
Date | Goal |
1-Feb | 0 |
2-Feb | 1 |
3-Feb | 1 |
4-Feb | 1 |
1-Mar | 1 |
2-Mar | 1 |
3-Mar | 0 |
4-Mar | 0 |
5-Mar | 1 |
6-Mar | 1 |
1-Apr | 0 |
2-Apr | 1 |
3-Apr | 0 |
4-Apr | 1 |
5-Apr | 0 |
6-Apr | 0 |
Want (Exclude last month) it could be any in this example its April
Date | Goal |
1-Feb | 0 |
2-Feb | 1 |
3-Feb | 1 |
4-Feb | 1 |
1-Mar | 1 |
2-Mar | 1 |
3-Mar | 0 |
4-Mar | 0 |
5-Mar | 1 |
6-Mar | 1 |
Ok if those are datetime values instead of date
try
having datepart(date)<intnx('month',max(datepart(date)),0,'b');
Hi @AZIQ1 Could you please post the date value in full ?
Date | Goal |
2/1/2019 | 0 |
2/2/2019 | 1 |
2/3/2019 | 1 |
2/4/2019 | 1 |
3/1/2019 | 1 |
3/2/2019 | 1 |
3/3/2019 | 0 |
3/4/2019 | 0 |
3/5/2019 | 1 |
3/6/2019 | 1 |
4/1/2019 | 0 |
4/2/2019 | 1 |
4/3/2019 | 0 |
4/4/2019 | 1 |
4/5/2019 | 0 |
4/6/2019 | 0 |
data have;
input Date :mmddyy10. Goal;
format date date9.;
cards;
2/1/2019 0
2/2/2019 1
2/3/2019 1
2/4/2019 1
3/1/2019 1
3/2/2019 1
3/3/2019 0
3/4/2019 0
3/5/2019 1
3/6/2019 1
4/1/2019 0
4/2/2019 1
4/3/2019 0
4/4/2019 1
4/5/2019 0
4/6/2019 0
;
proc sql;
create table want as
select *
from have
having date<intnx('month',max(date),0,'b');
quit;
Is that what you wanted? or perhaps some grouping variable needs to be accounted for?
Yes it seems like the solution but my date variable is in datetime25 format ?
Ok if those are datetime values instead of date
try
having datepart(date)<intnx('month',max(datepart(date)),0,'b');
Thank you so much.
Best
You should also indicate how we know what interval you are selecting from. And is it always just a single "month" or all dates prior to something?
You actually do no show a complete date value so the question that needs to be asked: Is your value actually a SAS date value or some pseudo-random collection of digits and letters that occasionally look like part of date such as 1-Feb?
I think that you actually mean "latest month". Most of us will interpret "last month" as the month before something.
Date | Goal | quarter |
1/1/2019 | 0 | 20191 |
1/2/2019 | 1 | 20191 |
1/3/2019 | 1 | 20191 |
1/4/2019 | 1 | 20191 |
1/5/2019 | 1 | 20191 |
1/6/2019 | 1 | 20191 |
1/7/2019 | 0 | 20191 |
1/8/2019 | 0 | 20191 |
2/1/2019 | 1 | 20191 |
2/2/2019 | 1 | 20191 |
2/3/2019 | 0 | 20191 |
2/4/2019 | 1 | 20191 |
3/1/2019 | 0 | 20191 |
3/2/2019 | 1 | 20191 |
3/3/2019 | 0 | 20191 |
3/4/2019 | 0 | 20191 |
Thank you,
If i select the field quarter as 20191 i want to run a proc summary, but the summary should not include March data
proc sort data = have;
by quarter;
run;
proc summary data = have;
var Goal;
output out=want sum=;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.