BookmarkSubscribeRSS Feed
Sisiwater
Calcite | Level 5

 

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Tom
Super User Tom
Super User

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.

Sisiwater
Calcite | Level 5

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

AnnaBrown
Community Manager

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


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2451 views
  • 0 likes
  • 4 in conversation