topic Re: Using PROC SQL MAX and Group by Function to select oldest date. in SAS Programming

Using PROC SQL MAX and Group by Function to select oldest date.

rebelde52 — Fri, 22 Sep 2023 07:14:47 GMT

Hello,

I have a small subset of patient data that that includes all their specimen dates. I also have a Pending column where some rows are associated with a "YES" for the Pending column. However I want to create a new column with a "Pending Max Date" where it takes the oldest date in their specimen date and repeats for all their rows so that I can compare the dates for an exclusion.

I was thinking of using Proc SQL and using the max and group by function but I have little experience with proc sql so any help would be great!

Below is what i'd like my table to look like.

Specimendate_all	Patient_ID	Pending	specimen pending max
3/29/22	AB1	NO	4/5/22
3/29/22	AB1	NO	4/5/22
3/29/22	AB1	YES	4/5/22
3/29/22	AB1	NO	4/5/22
4/5/22	AB1	NO	4/5/22
4/5/22	AB1	YES	4/5/22
4/5/22	AB1	YES	4/5/22
4/5/22	AB1	NO	4/5/22

Re: Using PROC SQL MAX and Group by Function to select oldest date.

PeterClemmensen — Fri, 22 Sep 2023 07:21:05 GMT

The oldest date and the MAX date are two different things.

I think you want to do this

data have;
input Specimendate_all :mmddyy10. Patient_ID $ Pending $;
format Specimendate_all mmddyy10.;
infile datalines dlm = ',' missover;
datalines;
03/29/22,AB1,NO  
03/29/22,AB1,NO  
03/29/22,AB1,YES 
03/29/22,AB1,NO  
04/05/22,AB1,NO  
04/05/22,AB1,YES 
04/05/22,AB1,YES 
04/05/22,AB1,NO  
;


proc sql;
   create table want as
   select *, max(Specimendate_all) as specimenpendingmax format = mmddyy10.
   from have
   ;
quit;

Re: Using PROC SQL MAX and Group by Function to select oldest date.

LinusH — Fri, 22 Sep 2023 07:22:54 GMT

It looks like you want the newest date, not the oldest...?

Untested:

proc sql;
   create table want as
      select *,
               max(case pending when 'YES' then specimentdata_all else 0 end) as specimen_pending_max
      from have
      group by patient_id;
quit;