data example;
input $record_id education mother;
datalines;
1 2 1
1 . 0
1 1 0
1 . 0
2 . 0
2 3 1
3 4 1
3 . 0
;
run;
*I want it to look like this;
data example;
input $record_id education mother;
datalines;
1 2 1
1 2 0
1 2 0
1 2 0
2 3 1
2 3 0
3 4 1
3 4 0
;
run;
data want;
do until (last.record_id);
set have;
by record_id;
if mother = 1 then _ed = education;
end;
do until (last.record_id);
set have;
by record_id;
education = _ed;
output;
end;
drop _ed;
run;
So you want the max of education to carry over to all other observations for the same record_id?
Close! I want the education value for the observations where mother = 1 to carry over for all the other observations with the same record _id. It isn't always the max value or the first value (within that record_id).
data want;
do until (last.record_id);
set have;
by record_id;
if mother = 1 then _ed = education;
end;
do until (last.record_id);
set have;
by record_id;
education = _ed;
output;
end;
drop _ed;
run;
That was amazing! Thank you so much!
Here's an SQL-based approach:
data example;
input record_id $ education mother;
datalines;
1 2 1
1 . 0
1 1 0
1 . 0
2 . 0
2 3 1
3 4 1
3 . 0
;
run;
proc sql;
create table want as
select A.record_id
,B.education_max
,A.mother
from example as A
left join
(select record_id
,max(education) as education_max
from example
where mother = 1
group by record_id
) as B
on A.record_id = B.record_id
;
quit;
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.