topic Re: Exclude last month from date in SAS Programming

Exclude last month from date

AZIQ1 — Wed, 24 Apr 2019 22:53:40 GMT

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

Re: Exclude last month from date

novinosrin — Wed, 24 Apr 2019 23:01:46 GMT

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

Re: Exclude last month from date

AZIQ1 — Wed, 24 Apr 2019 23:06:14 GMT

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

Re: Exclude last month from date

ballardw — Wed, 24 Apr 2019 23:08:50 GMT

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.

Re: Exclude last month from date

novinosrin — Wed, 24 Apr 2019 23:26:34 GMT

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;

Re: Exclude last month from date

AZIQ1 — Thu, 25 Apr 2019 00:02:31 GMT

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;

Re: Exclude last month from date

novinosrin — Thu, 25 Apr 2019 00:44:06 GMT

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

Re: Exclude last month from date

AZIQ1 — Thu, 25 Apr 2019 00:45:21 GMT

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

Re: Exclude last month from date

novinosrin — Thu, 25 Apr 2019 00:49:23 GMT

Ok if those are datetime values instead of date

try

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

Re: Exclude last month from date

AZIQ1 — Thu, 25 Apr 2019 00:50:13 GMT

Thank you so much.

Best