Data have:
visit_id study_id delivery_visit multiple_gestation
1 1 0 0
2 1 0 0
3 1 1 0
4 2 0 1
5 2 1 0
6 3 0 0
7 3 0 0
8 3 1 0
9 4 0 0
10 4 1 1
Here is what I need to do:
For any mothers that have multiple_gestation=1 for any of the visits, then multiple_gestation=1 for all visits.
The mothers with multiple_gestation=0 would stay the same -- 0 for all visits.
Data want:
visit_id study_id delivery_visit multiple_gestation
1 1 0 0
2 1 0 0
3 1 1 0
4 2 0 1
5 2 1 1
6 3 0 0
7 3 0 0
8 3 1 0
9 4 0 1
10 4 1 1
Is there a term for what I'm trying to do and what is the code?
Thanks!
Coming up with a name will be difficult since there are a few ways to approach the problem. Here's one way:
data want;
do until (last.study_id);
set have;
by study_id;
highest = max(highest, multiple_gestation);
end;
do until (last.study_id);
set have;
by study_id;
multiple_gestation = highest;
output;
end;
drop highest;
run;
The top loop reads all observations for a STUDY_ID, and finds the largest value for MULTIPLE_GESTATION.
The bottom DO loop reads the same observations, assigns a (possibly) new value to MULTIPLE_GESTATION, and outputs the observations.
Coming up with a name will be difficult since there are a few ways to approach the problem. Here's one way:
data want;
do until (last.study_id);
set have;
by study_id;
highest = max(highest, multiple_gestation);
end;
do until (last.study_id);
set have;
by study_id;
multiple_gestation = highest;
output;
end;
drop highest;
run;
The top loop reads all observations for a STUDY_ID, and finds the largest value for MULTIPLE_GESTATION.
The bottom DO loop reads the same observations, assigns a (possibly) new value to MULTIPLE_GESTATION, and outputs the observations.
Hello,
Here is a solution with proc sql.
proc sql;
CREATE TABLE WANT AS
SELECT visit_id, study_id, delivery_visit,
max(multiple_gestation) AS multiple_gestation
FROM have
GROUP BY study_id
ORDER BY visit_id;
quit;
H
Both of these solutions worked perfect. Thanks to you both!
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.