topic Re: finding index date of treatment in New SAS User

finding index date of treatment

Banke — Thu, 29 Sep 2022 23:44:50 GMT

Hello everyone, I would like to use sql to get distinct observations for each patient that corresponds to their earliest fill date. I saw the attached code on the forum but it does not give the output as distinct observations. Thank you

/**what i have***/

patient ID fill_date

patient 1 02/23/2001

patient 1 02/25/2001

patient 1 03/14/2003

patient 2 01/01/2006

patient 2 10/14/2001

patient 2 08/25/2008

patient 3 07/14/2004

/**what i want**/

patient ID fill_date

patient 1 02/23/2001

patient 2 10/14/2001

patient 3 07/14/2004

proc sql;
create table want as
select *
from have
group by PATIENT_ID, FILL_DATE
having FILL_DATE = MIN (FILL_DATE) ;
quit;

Re: finding index date of treatment

ballardw — Thu, 29 Sep 2022 23:57:57 GMT

Try

proc sql;
create table want as
select * 
from have
group by PATIENT_ID
having FILL_DATE = MIN (FILL_DATE) ;
quit;

When you have Group by Patient_id, Fill_date you have told SQL that combinations of Patient and date are what the Min is calculated from. In the example that means the Min of each date, not each patient

Re: finding index date of treatment

Banke — Fri, 30 Sep 2022 00:04:51 GMT

Thank you, it gives me more observation than i started with (750,758), and a caution in my log as shown

 proc sql;
56   create table XX as
57   select *
58   from YY
59   group by PATIENT_ID
60   having FILL_DATE = MIN (FILL_DATE) ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.XX created, with 1963821 rows and 7 columns.

61   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           5.70 seconds
      cpu time            5.33 seconds

Re: finding index date of treatment

Tom — Fri, 30 Sep 2022 01:41:32 GMT

I doubt it (unless perhaps YY is actually a reference to a dataset (table) in some external database that does strange things).

For example try this query whose form matches yours.

685  proc sql;
686  create table class as select * from sashelp.class;
NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

687  create table youngest as select * from class group by sex having age=min(age);
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.YOUNGEST created, with 2 rows and 5 columns.

Now you might get more observations than there are patients if there are multiple observations for a patient that tie for the earliest date.

Re: finding index date of treatment

Tom — Fri, 30 Sep 2022 01:44:30 GMT

If you just want the earliest date then tell SQL that fact.

create table want as
select PATIENT_ID
     , min(FILL_DATE) as INDEX_DATE format=yymmdd10.
from have
group by PATIENT_ID
;

Re: finding index date of treatment

andreas_lds — Fri, 30 Sep 2022 06:45:25 GMT

Your dates are dates, not strings looking like dates?

Re: finding index date of treatment

Banke — Fri, 30 Sep 2022 14:47:08 GMT

This worked

Re: finding index date of treatment

Banke — Fri, 30 Sep 2022 14:47:20 GMT

dates