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;
For LENGTH, use WHERE instead of HAVING:
where length ne .
instead of
and length ne .
The rest can stay the same.
For LENGTH, use WHERE instead of HAVING:
where length ne .
instead of
and length ne .
The rest can stay the same.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.