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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.