BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Badshaah
Obsidian | Level 7
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.
1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

15 REPLIES 15
ballardw
Super User

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.

Badshaah
Obsidian | Level 7
It's form base table having variable in date9 format
Jagadishkatam
Amethyst | Level 16
Could you please provide sample data with expected output to help you better.
Thanks,
Jag
Badshaah
Obsidian | Level 7
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
Badshaah
Obsidian | Level 7
We have two calculate only last month which max of month and .. Second last month
Astounding
PROC Star

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?

Badshaah
Obsidian | Level 7
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.
Astounding
PROC Star

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.

SuryaKiran
Meteorite | Level 14

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
Badshaah
Obsidian | Level 7
Thanks suryakiran & Astounding.
Astounding
PROC Star

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

Badshaah
Obsidian | Level 7
Then what's the solution for that
Astounding
PROC Star

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.

Badshaah
Obsidian | Level 7
Grand salute ..to u .. Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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