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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.