BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

View solution in original post

3 REPLIES 3
ChrisNZ
Tourmaline | Level 20

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

Thank you so much!!!

 

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

 

So grateful for your quick help 🐕!!!

Amir
PROC Star

@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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1014 views
  • 5 likes
  • 3 in conversation