Exclude last month from date

[AZIQ1]

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

[novinosrin]

Hi @AZIQ1  Could you please post the date value in full ?

[AZIQ1]

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

[novinosrin]

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;

[novinosrin]

Is that what you wanted? or perhaps some grouping variable needs to be accounted for?

[AZIQ1]

Yes it seems like the solution but my date variable is in datetime25 format ?

[novinosrin · Accepted solution]

Ok if those are datetime values instead of date

try

having datepart(date)<intnx('month',max(datepart(date)),0,'b');

[AZIQ1]

Thank you so much.

Best

[ballardw]

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.

[AZIQ1]

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;