topic How to select first obs based on sorting by several variables? in SAS Data Management

How to select first obs based on sorting by several variables?

Sisiwater — Tue, 05 Sep 2017 14:24:37 GMT

Dear all,

Sorry to bother you for a question for selecting of first observation based on sorting by several variables. The dataset was sorted by ID, diagnosis and date of diagnosis. I would like to select the first observation with the first diagnosed date but the same diagnosis.

I tried to use "first.ID and first.date" or "first.ID and first.date and first.diagnosis"but it seems not correct. I attached the test dataset. The records that I would like to select were highlighted in yellow.

Many thanks in advance!

Best

Sisi

Re: How to select first obs based on sorting by several variables?

RW9 — Tue, 05 Sep 2017 14:31:04 GMT

Hi,

Sorry, I can't download Excel files, post test data as a datastep in the post.

The principal should be sound, sort the data in the order you need, then last.<lowest of by group>.

For example to get last date in each diagnosis:

proc sort data=have;
  by id diagnosis date;
run;

data want;
  set have;
  by id diagnosis date;
  if last.diagnosis then output;
run;

Re: How to select first obs based on sorting by several variables?

Tom — Tue, 05 Sep 2017 14:29:16 GMT

If you want the first time this DX appeared for this ID then sort BY ID DX DATE and select the records where FIRST.DX is true.

If you want the first DX for this ID then sort BY ID DATE and take FIRST.ID.

Re: How to select first obs based on sorting by several variables?

Sisiwater — Tue, 12 Sep 2017 06:29:29 GMT

Thank you so much! I thought I replied through email but it seems not showed. Sorry about it! Thank you again!

Re: How to select first obs based on sorting by several variables?

AnnaBrown — Tue, 12 Sep 2017 14:25:08 GMT

I'm glad you found some useful info, Sisiwater! If one of the replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

By the way, I deleted one of your replies that was duplicate. Maybe the first one didn't come through right away when you replied via email.

Thanks!
Anna