Hello all, I have the following data and code below. What I would like to do is choose the last monyr by id. Example the id listed below I would like to choose 04/26/2017 and 05/03/2017. I tried using the code below but no obs are chosen.
data date;
infile datalines delimiter = ',';
input ID $ DATE:mmddyy10. ;
format date mmddyy10.;
datalines;
1, 05/03/2017
1, 05/02/2017
1, 04/26/2017
1, 04/18/2017
1, 04/10/2017
;
run;
data date;
set date;
MONYR=DATE;
format monyr monyy7.;
run;
proc sort data=date;
by id monyr date;
run;
data want_date;
set date;
by id monyr date;
if last.monyr;
run;
data date;
set date;
month=intnx('month',date,0,'b');
run;
proc sort data=date;
by id month date;
run;
data want_date;
set date;
by id month;
if last.month;
drop month;
run;
Your variable MONYR serves no purpose, it has the exact same value as the variable DATE, and so adds nothing to the solution..Formatting a variable only changes its appearance, it does not change the underlying value which is used for sorting and all arithmetic.
Instead, you want a variable (which I have named MONTH), which is a valid SAS date value, hence the use of the INTNX function, which here finds the first day of each month, and essentially gives you a month/year that can be sorted properly.
data date;
set date;
month=intnx('month',date,0,'b');
run;
proc sort data=date;
by id month date;
run;
data want_date;
set date;
by id month;
if last.month;
drop month;
run;
Your variable MONYR serves no purpose, it has the exact same value as the variable DATE, and so adds nothing to the solution..Formatting a variable only changes its appearance, it does not change the underlying value which is used for sorting and all arithmetic.
Instead, you want a variable (which I have named MONTH), which is a valid SAS date value, hence the use of the INTNX function, which here finds the first day of each month, and essentially gives you a month/year that can be sorted properly.
Got it. Thank you!
The problem is MONYY is a SAS date value (number of days from 01/01/1960) so each date is the last occurrance of that date.
What you need to do is change the MONYY to a character value and format it:
data date;
infile datalines delimiter = ',';
input ID $ DATE:mmddyy10. ;
format date mmddyy10.;
datalines;
1, 05/03/2017
1, 05/02/2017
1, 04/26/2017
1, 04/18/2017
1, 04/10/2017
;
run;
/* Change this datastep */
data date;
set date;
MONYR=putn(DATE,"monyy7.");
run;
proc sort data=date;
by id monyr date;
run;
data want_date;
set date;
by id monyr date;
if last.monyr;
run;
Character formatted dates as MONYY. do not sort properly. There's really no reason to use character strings as dates here.
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.