BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
CathyVI
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
mkeintz
PROC Star

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 2 replies
  • 1125 views
  • 3 likes
  • 3 in conversation