topic How do I make one observation's value of a variable all of the values of that variable by group? in SAS Programming

How do I make one observation's value of a variable all of the values of that variable by group?

mbayer — Thu, 08 Jun 2023 03:11:02 GMT

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;

Re: How do I make one observation's value of a variable all of the values of that variable by group?

Kurt_Bremser — Thu, 08 Jun 2023 03:49:02 GMT

So you want the max of education to carry over to all other observations for the same record_id?

Re: How do I make one observation's value of a variable all of the values of that variable by group?

mbayer — Thu, 08 Jun 2023 03:54:05 GMT

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).

Re: How do I make one observation's value of a variable all of the values of that variable by group?

Kurt_Bremser — Thu, 08 Jun 2023 04:07:51 GMT

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;

Re: How do I make one observation's value of a variable all of the values of that variable by group?

SASKiwi — Thu, 08 Jun 2023 04:12:46 GMT

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;

Re: How do I make one observation's value of a variable all of the values of that variable by group?

mbayer — Thu, 08 Jun 2023 04:17:12 GMT

That was amazing! Thank you so much!