DATA Step, Macro, Functions and more

How filter last month and previous month data from date column

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How filter last month and previous month data from date column

We have single data table in which we only required last month data ,
So i can calculate last date by max date but ..need logic for second last month.

Accepted Solutions
Solution
‎12-14-2016 04:37 AM
Frequent Contributor
Posts: 136

Re: How filter last month and previous month data from date column

data one ;

infile datalines;

retain obs id jobcat transmonth wage rating;

format transmonth date9. wage dollar10.2 ;

input obs id jobcat $ transmonth date9. wage dollar10.2 rating;

datalines;

1 1254 one 29AUG1998 $11000.00 2

2 9936 two 17OCT1998 $5,000.00 0

3 7529 two 04NOV1998 $9,000.00 1

4 9154 one 25NOV1998 $10000.00 2

5 7741 two 18NOV1998 $9,500.00 3

6 8896 two 11OCT1998 $9,600.00 4

7 6658 one 01MAY1998 $12000.00 4

8 7854 two 27OCT1998 $9,600.00 3

9 9458 two 13NOV1998 $8,999.00 1

10 7887 two 19OCT1998 $9,050.00 0

;

run;

/* -Finding the max date and storing in a macro*/

proc sql noprint;

select max(transmonth) INTO : maxdate

from one;

quit;

%put &maxdate;

run;

/* -Getting all the records with months as max date and month prior */

data two;

retain obs id jobcat transmonth wage rating;

FORMAT transmonth MONYY7.;

set one;

if month(transmonth)= month(&maxdate) OR month(transmonth)= month(&maxdate)-1;

run;

Thanks,
Suryakiran

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How filter last month and previous month data from date column

[ Edited ]

First question: are your dates actually SAS date or datetime valued variables, character or something else? The functions to manipulate dates require SAS date, time or datetime values and to know which you have.

Contributor
Posts: 34

Re: How filter last month and previous month data from date column

It's form base table having variable in date9 format
Trusted Advisor
Posts: 1,137

Re: How filter last month and previous month data from date column

Could you please provide sample data with expected output to help you better.
Thanks,
Jag
Contributor
Posts: 34

Re: How filter last month and previous month data from date column

employee_
Obs id jobcat transmonth wage rating

1 1254 one 29AUG1998 $11000.00 2
2 9936 two 17OCT1998 $5,000.00 0
3 7529 two 04NOV1998 $9,000.00 1
4 9154 one 25NOV1998 $10000.00 2
5 7741 two 18NOV1998 $9,500.00 3
6 8896 two 11OCT1998 $9,600.00 4
7 6658 one 01MAY1999 $12000.00 4
8 7854 two 27OCT1998 $9,600.00 3
9 9458 two 13NOV1998 $8,999.00 1
10 7887 two 19OCT1998 $9,050.00 0
Contributor
Posts: 34

Re: How filter last month and previous month data from date column

We have two calculate only last month which max of month and .. Second last month
Super User
Posts: 5,498

Re: How filter last month and previous month data from date column

[ Edited ]

So in the sample data you provided, 25NOV1998 is the maximum value for the incoming dates.  What would you like the beginning and ending points to be for your time period?

Contributor
Posts: 34

Re: How filter last month and previous month data from date column

It's just sample data .. If 28nov2016 is max then max -1 months 30oct2016 is also needed but we have to convert date9. To monyy7. Format which only extract month and year.
Super User
Posts: 5,498

Re: How filter last month and previous month data from date column

OK.  Assuming you have the max date already calculated:

 

cutoff_date = intnx('month', max_date, -1, 'e');

 

This gets you a SAS date value that you can express in any format that you choose.

Solution
‎12-14-2016 04:37 AM
Frequent Contributor
Posts: 136

Re: How filter last month and previous month data from date column

data one ;

infile datalines;

retain obs id jobcat transmonth wage rating;

format transmonth date9. wage dollar10.2 ;

input obs id jobcat $ transmonth date9. wage dollar10.2 rating;

datalines;

1 1254 one 29AUG1998 $11000.00 2

2 9936 two 17OCT1998 $5,000.00 0

3 7529 two 04NOV1998 $9,000.00 1

4 9154 one 25NOV1998 $10000.00 2

5 7741 two 18NOV1998 $9,500.00 3

6 8896 two 11OCT1998 $9,600.00 4

7 6658 one 01MAY1998 $12000.00 4

8 7854 two 27OCT1998 $9,600.00 3

9 9458 two 13NOV1998 $8,999.00 1

10 7887 two 19OCT1998 $9,050.00 0

;

run;

/* -Finding the max date and storing in a macro*/

proc sql noprint;

select max(transmonth) INTO : maxdate

from one;

quit;

%put &maxdate;

run;

/* -Getting all the records with months as max date and month prior */

data two;

retain obs id jobcat transmonth wage rating;

FORMAT transmonth MONYY7.;

set one;

if month(transmonth)= month(&maxdate) OR month(transmonth)= month(&maxdate)-1;

run;

Thanks,
Suryakiran
Contributor
Posts: 34

Re: How filter last month and previous month data from date column

Thanks suryakiran & Astounding.
Super User
Posts: 5,498

Re: How filter last month and previous month data from date column

Just be careful to adjust the solution for January.  As is, the program will look for month=0 instead of month=12.

Contributor
Posts: 34

Re: How filter last month and previous month data from date column

Then what's the solution for that
Super User
Posts: 5,498

Re: How filter last month and previous month data from date column

Based on the tools that I had posted originally:

 

if ( intnx('month', max_date, -1, 'b') <= transmonth <= intnx('month', max_date, -1, 'e') );

 

In fact, the solution as posted only looks at month.  If you have a data set that includes more than one year of data, you can be selecting based on month only, including data from a previous year.

Contributor
Posts: 34

Re: How filter last month and previous month data from date column

Grand salute ..to u .. Thanks
☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 931 views
  • 2 likes
  • 5 in conversation