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;
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.
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?
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.
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;
Just be careful to adjust the solution for January. As is, the program will look for month=0 instead of month=12.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.