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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.