Selecting first non-missing value for each subject

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Selecting first non-missing value for each subject

I have a dataset in which i need to identify the first non missing subject within a dose. 

My data looks like below

 

subject date         dose
10002 1/27/2015 10MG
10002 2/24/2015 10MG
10002 6/1/2015   20MG
10002 9/29/2015 10MG
10002 1/24/2016 10MG

 

I want my final dataset to look like below,

 

subject     date           dose
10002    1/27/2015   10MG
10002    6/1/2015     20MG
10002    9/29/2015   10MG

 

Is there any particular way to do this? If i do first.dose, its giving me only first 2 records of my final dataset. 


Accepted Solutions
Solution
‎05-11-2017 09:14 PM
PROC Star
Posts: 7,363

Re: Selecting first non-missing value for each subject

data have;
  input subject date mmddyy10. dose $;
  format date mmddyy10.;
  cards;
10002 1/27/2015 10MG
10002 2/24/2015 10MG
10002 6/1/2015   20MG
10002 9/29/2015 10MG
10002 1/24/2016 10MG
;

data want;
  set have;
  by subject dose notsorted;
  if first.dose;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Solution
‎05-11-2017 09:14 PM
PROC Star
Posts: 7,363

Re: Selecting first non-missing value for each subject

data have;
  input subject date mmddyy10. dose $;
  format date mmddyy10.;
  cards;
10002 1/27/2015 10MG
10002 2/24/2015 10MG
10002 6/1/2015   20MG
10002 9/29/2015 10MG
10002 1/24/2016 10MG
;

data want;
  set have;
  by subject dose notsorted;
  if first.dose;
run;

Art, CEO, AnalystFinder.com

New Contributor
Posts: 4

Re: Selecting first non-missing value for each subject

Thanks Art. But this gives me only 2 records.

 

subject     date           dose
10002    1/27/2015   10MG
10002    6/1/2015     20MG

 

I need the first non missing record every time the dose changes.

PROC Star
Posts: 7,363

Re: Selecting first non-missing value for each subject

[ Edited ]

You're doing something other than what I did. I get all 3 records.

 

Is it possible that you sorted your file before running the code I suggested?

 

Art, CEO, AnalystFinder.com

New Contributor
Posts: 4

Re: Selecting first non-missing value for each subject

Yes, you are right. I had sorted my dataset before. I corrected it and it gives me 3 records.

 

Thank you very much.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 152 views
  • 0 likes
  • 2 in conversation