SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
New Contributor
Posts: 3

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

 

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

Super User
Super User
Posts: 8,380

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

[ Edited ]
Posted in reply to Sisiwater

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;
Super User
Super User
Posts: 7,260

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

Posted in reply to Sisiwater

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.

New Contributor
Posts: 3

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

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

Community Manager
Posts: 605

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

Posted in reply to Sisiwater

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

Ask a Question
Discussion stats
  • 4 replies
  • 176 views
  • 0 likes
  • 4 in conversation