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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.