DATA Step, Macro, Functions and more

Finding the second minimum if observation with first minimum does not satisfy other conditions

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Finding the second minimum if observation with first minimum does not satisfy other conditions

Hi, I am trying to extract only observations with the smallest absolute value for v1datediff. There are multiple rows of data for each unique individual, so not only do I want to extract the minimum v1datediff, but I also want to make sure that if the minimum v1datediff has missing values for another column, then I would take the next minimum with complete data. Is this doable in PROC SQL or base SAS?

 

participant_id     length     absv1datediff
1                         2            1
1                         2.2          9
1                         .            2
1                         2.1          3
2                         .            0
2                         1.9          1
2                         1.9          5

So for the above dataset, I'd like to extract, for participant 1, the first row (where length is 2). For participant 2, the minimum absv1datediff is 0 (row 5), but since it has missing length, I'd like to extract the NEXT minimum, which is row 6. 

 

I tried something like the following:

 

proc sql;
	create table v1final as
	select *, abs(v1datediff) as absv1datediff, abs(ehr_height_cm - inflengthcm_v1) as measurement_diff
	from visit1_merged
	group by participant_id
	having absv1datediff = min(absv1datediff) and length ne .
	;
quit;

Accepted Solutions
Solution
‎04-05-2018 11:43 AM
Super User
Posts: 6,751

Re: Finding the second minimum if observation with first minimum does not satisfy other conditions

For LENGTH, use WHERE instead of HAVING:

 

where length ne .

 

instead of

 

and length ne .

 

The rest can stay the same.

View solution in original post


All Replies
Solution
‎04-05-2018 11:43 AM
Super User
Posts: 6,751

Re: Finding the second minimum if observation with first minimum does not satisfy other conditions

For LENGTH, use WHERE instead of HAVING:

 

where length ne .

 

instead of

 

and length ne .

 

The rest can stay the same.

Contributor
Posts: 29

Re: Finding the second minimum if observation with first minimum does not satisfy other conditions

Posted in reply to Astounding
This is perfect. Thank you!
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 151 views
  • 0 likes
  • 2 in conversation