BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AZIQ1
Quartz | Level 8

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:

DateGoal
1-Feb0
2-Feb1
3-Feb1
4-Feb1
1-Mar1
2-Mar1
3-Mar0
4-Mar0
5-Mar1
6-Mar1
1-Apr0
2-Apr1
3-Apr0
4-Apr1
5-Apr0
6-Apr0

 

Want (Exclude last month) it could  be any in this example its April 

DateGoal
1-Feb0
2-Feb1
3-Feb1
4-Feb1
1-Mar1
2-Mar1
3-Mar0
4-Mar0
5-Mar1
6-Mar1
  
  
  
  
  
  
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Ok if those are datetime values instead of date

 

try

 

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

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

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

AZIQ1
Quartz | Level 8
DateGoal
2/1/20190
2/2/20191
2/3/20191
2/4/20191
3/1/20191
3/2/20191
3/3/20190
3/4/20190
3/5/20191
3/6/20191
4/1/20190
4/2/20191
4/3/20190
4/4/20191
4/5/20190
4/6/20190
novinosrin
Tourmaline | Level 20
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
Tourmaline | Level 20

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

AZIQ1
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20

Ok if those are datetime values instead of date

 

try

 

having datepart(date)<intnx('month',max(datepart(date)),0,'b');
AZIQ1
Quartz | Level 8

Thank you so much.

Best

ballardw
Super User

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
Quartz | Level 8
DateGoalquarter
1/1/2019020191
1/2/2019120191
1/3/2019120191
1/4/2019120191
1/5/2019120191
1/6/2019120191
1/7/2019020191
1/8/2019020191
2/1/2019120191
2/2/2019120191
2/3/2019020191
2/4/2019120191
3/1/2019020191
3/2/2019120191
3/3/2019020191
3/4/2019020191

 

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1796 views
  • 2 likes
  • 3 in conversation