topic Re: Choosing obs by monyr in SAS Programming

Choosing obs by monyr

luvscandy27 — Wed, 03 Mar 2021 15:37:59 GMT

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;

Re: Choosing obs by monyr

PaigeMiller — Wed, 03 Mar 2021 15:57:16 GMT

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.

Re: Choosing obs by monyr

luvscandy27 — Wed, 03 Mar 2021 15:58:06 GMT

Got it. Thank you!

Re: Choosing obs by monyr

AMSAS — Wed, 03 Mar 2021 15:58:19 GMT

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;

Re: Choosing obs by monyr

PaigeMiller — Wed, 03 Mar 2021 16:02:36 GMT

Character formatted dates as MONYY. do not sort properly. There's really no reason to use character strings as dates here.