identify first ID and the first drug name by prescription date

CathyVI — Fri, 19 Jul 2024 15:51:33 GMT

Hello,

I want to identify the first record and first drug by the prescription date for each individual by ID. Here is a sample dataset and my intended output. Thanks

data have (index=(ID));
  input ID $ 1-7 prescrpt_dt drug_name $ 18-30  ;
  format prescrpt_dt date.;
  informat prescrpt_dt date.;
  datalines; 
1685609	6-Sep-16	ACETAZOLAMIDE
1866294	25-Nov-20	LAMOTRIGINE
1866294	20-Apr-17	TOPAMAX
1866294	1-Aug-17	TOPAMAX
1866294	20-Oct-17	TOPAMAX
1866294	8-Feb-18	TOPAMAX
1866294	15-Nov-16	TOPIRAMATE
1866294	25-Nov-16	TOPIRAMATE
1866294	19-Apr-18	TOPIRAMATE
1866294	12-Jun-18	TOPIRAMATE
1866294	4-Dec-19	TOPIRAMATE
2291100	12-Mar-22	LEVETIRACETAM
2329497	10-Apr-23	LEVETIRACETAM
2329497	9-Jul-23	LEVETIRACETAM
2329497	7-Oct-23	LEVETIRACETAM
2329497	5-Jan-24	LEVETIRACETAM
2388002	26-Apr-19	LAMOTRIGINE
2388002	8-Jul-19	LAMOTRIGINE
2388002	6-Oct-19	LAMOTRIGINE
2388002	6-Jan-20	LAMOTRIGINE
2388002	6-Apr-20	LAMOTRIGINE
2388002	4-Jul-20	LAMOTRIGINE
2388002	2-Oct-20	LAMOTRIGINE
2388002	11-Dec-20	LAMOTRIGINE
2388002	11-Mar-21	LAMOTRIGINE
2388002	9-Jun-21	LAMOTRIGINE
2388002	7-Sep-21	LAMOTRIGINE
2388002	7-Dec-21	LAMOTRIGINE
2388002	6-Mar-22	LAMOTRIGINE
2388002	4-Jun-22	LAMOTRIGINE
2388002	2-Sep-22	LAMOTRIGINE
2388002	28-Nov-22	LAMOTRIGINE
2388002	28-Feb-23	LAMOTRIGINE
2388002	27-May-23	LAMOTRIGINE
2388002	25-Aug-23	LAMOTRIGINE
2388002	23-Nov-23	LAMOTRIGINE
2388002	4-Feb-19	LEVETIRACETAM
2480879	2-Jul-23	LAMOTRIGINE
2480879	16-Oct-23	LAMOTRIGINE
2480879	22-Dec-23	LAMOTRIGINE
2480879	2-Jul-23	LEVETIRACETAM
2480879	15-Oct-23	LEVETIRACETAM
2480879	22-Dec-23	LEVETIRACETAM
;
run;

For example: 1866294 I sorted the data so I can have the last drug name by id and remove duplicates but my coding is not doing what i want.

proc sort data=have;
by id drug_name descending prescription_dt; 
run;

data want;
set have;
by id drug_name;
if first.id and first.drug_name;
run;

Output

1685609	6-Sep-16	ACETAZOLAMIDE
1866294	4-Dec-19	TOPIRAMATE
1866294	8-Feb-18	TOPAMAX
1866294	25-Nov-20	LAMOTRIGINE
2291100	12-Mar-22	LEVETIRACETAM
2329497	5-Jan-24	LEVETIRACETAM
2388002	23-Nov-23	LAMOTRIGINE
2388002	4-Feb-19	LEVETIRACETAM
2480879	22-Dec-23	LAMOTRIGINE
2480879	22-Dec-23	LEVETIRACETAM

Re: identify first ID and the first drug name by prescription date

PaigeMiller — Fri, 19 Jul 2024 16:39:06 GMT

proc sort data=have;
by id drug_name descending prescription_dt; 
run;

data want;
set have;
by id drug_name;
if first.drug_name;
run;

Re: identify first ID and the first drug name by prescription date

mkeintz — Fri, 19 Jul 2024 18:47:47 GMT

This is the sort of thing PROC SUMMARY does, in a single pass of the data, with no need to sort:

proc summary data=have nway ;
  class ID  drug_name;
  var prescrpt_dt;
  output out=want (drop=_type_) max=max_dt;
run;

topic Re: identify first ID and the first drug name by prescription date in SAS Programming

identify first ID and the first drug name by prescription date

Re: identify first ID and the first drug name by prescription date

Re: identify first ID and the first drug name by prescription date