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

corkee — Thu, 05 Apr 2018 14:42:13 GMT

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;

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

Astounding — Thu, 05 Apr 2018 15:18:31 GMT

For LENGTH, use WHERE instead of HAVING:

where length ne .

instead of

and length ne .

The rest can stay the same.

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

corkee — Thu, 05 Apr 2018 15:44:10 GMT

This is perfect. Thank you!

topic Finding the second minimum if observation with first minimum does not satisfy other conditions in SAS Programming

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

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

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