How to replace missing values within household level data

[Tamara_UCD]

I have a dataset where individual subjects are uniquely identified by SUBJECTID. Each subject belongs to a household which is identified by the variable HOUSEID. There can be 1 or 2 subjects in each household. We asked a subset of questions ONLY of one member of the household. For subjects in the household who were not asked this series of questions, they currently have missing values for those particular variables in the dataset. I would like to input the other household member’s response in for these missing responses, and I would like this information to be created as a new variable (because I would like to keep the original variables intact). I would also like the original value for the respondent to be carried over into the new variable as well.

Briefly, here's what the data look like:
data temp;
input houseid subjectid hhincome hhtype;
datalines;
1001 10011 10000 2
1001 10012
1002 10021
1002 10022 25000 3
;

[Vasile01]

Hi,

I hope that I understood your particular requirements and this will help.
--

data temp;
input houseid subjectid hhincome hhtype;
datalines;
1001 10011 10000 2
1001 10012 . .
1002 10021 . .
1002 10022 25000 3
;

run;

proc sort data=temp out=temp2;
by houseid descending hhincome;
run;

data temp3;
set temp2;
by houseid;
retain hhincome2 0;
retain hhtype2 0;
if first.houseid=1 then
do;
hhincome2=hhincome;
hhtype2=hhtype;
end;
output;
run;

Warm regards,
Vasile

[deleted_user]

Hello,

here is another solution which will update all variables of the second household subject (except subjectid) with the values of the first subject :

[pre]
data x;

if first.houseid and ^missing(houseid) then link build;

set temp;
by houseid ;

if first.houseid then output;
return;

build:
set temp(keep=subjectid) point=_n_;
output;
run;
[/pre]

Marius

[deleted_user]

Do not forget about the sorting as I did 🙂

[pre]
proc sort data=temp;
by houseid descending hhincome descending hhtype;
run;
[/pre]

Marius

[Tamara_UCD]

Thank you for the suggestions everyone. I appreciate the help!!

[data_null__]

Seems like you can just merge on the income variables after renaming.

[pre]
data survey;
infile cards missover;
input houseid:$4. subjectid:$5. hhincome hhtype;
cards;
1001 10011 10000 2
1001 10012
1002 10021
1002 10022 25000 3
;;;;

data surver2;
merge
survey
survey
(
keep=houseid hh:
rename=(hhincome=income hhtype=type)
where=(not missing(income))
)
;
by houseid;
run;
proc print;
run;
[/pre]

[pre]
Obs houseid subjectid hhincome hhtype income type

1 1001 10011 10000 2 10000 2
2 1001 10012 . . 10000 2
3 1002 10021 . . 25000 3
4 1002 10022 25000 3 25000 3
[/pre]

[Vasile01]

Or with a SQL statement:


proc sql;
select *, sum(hhincome) as income, sum(hhtype) as type
from temp
group by houseid
;
quit;


Warm regards
Vasile