topic Re: PROC SQL DATES sorting wrong? in SAS Programming

PROC SQL DATES sorting wrong?

anonymous_user — Mon, 21 Jun 2021 06:41:20 GMT

Hi, how do I use min() and max() function in PROC SQL to get the right dates (earliest and latest dates) from a list of visit dates?

I feel like my code is right, but I'm getting a format wrong. PROC SQL is using the min/max functions correctly within the same months (02FEB2009 is less than 19FEB2009), but the functions mess up when the months are different (02NOV2009 is incorrectly less than 10OCT2009).

Please help!!! A million thanks to the wonderful SAS community 😁.

options fmtsearch=(lookup);
data aggreg;
	set svisit1-svisit&vars;
	by USUBJID VISITNUM rawdtc;
	VISIT=put(compress(put(VISITNUM,3.)),$VISITPRJ5457_VISIT.);
	rawdtc2=put(input(rawdtc,E8601DA10.),date10.);
	run;
	
proc sort data=aggreg out=aggreg2;
by USUBJID VISIT rawdtc2;

Proc sql;
create table xo as 
select USUBJID, VISIT, VISITNUM, rawdtc2,
 min(rawdtc2) as min_date,
 max(rawdtc2) as max_date 
from aggreg2
group by USUBJID, VISIT;

Re: PROC SQL DATES sorting wrong?

ChrisNZ — Mon, 21 Jun 2021 06:46:45 GMT

RAWDTC2 is a string, therefore April comes before February.

Replace

rawdtc2=put(input(rawdtc,E8601DA10.),date10.);

with

rawdtc2=input(rawdtc,E8601DA10.);

to have a date

Re: PROC SQL DATES sorting wrong?

anonymous_user — Mon, 21 Jun 2021 07:22:05 GMT

Thank you so much!!!

I've been staring at this error for an hour.

So grateful for your quick help 🐕!!!

Re: PROC SQL DATES sorting wrong?

Amir — Mon, 21 Jun 2021 14:32:00 GMT

@anonymous_user, glad it is now working.

For best practice and further appreciation, you should be able to accept the response from @ChrisNZ as the solution (not my post).

Kind regards,

Amir.