topic Re: How to replace missing values within household level data in SAS Programming

How to replace missing values within household level data

Tamara_UCD — Thu, 17 Feb 2011 21:43:43 GMT

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
;

Re: How to replace missing values within household level data

Vasile01 — Fri, 18 Feb 2011 01:05:11 GMT

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

Re: How to replace missing values within household level data

deleted_user — Fri, 18 Feb 2011 16:40:39 GMT

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

Re: How to replace missing values within household level data

deleted_user — Fri, 18 Feb 2011 16:43:19 GMT

Do not forget about the sorting as I did 🙂

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

Marius

Re: How to replace missing values within household level data

Tamara_UCD — Fri, 18 Feb 2011 19:04:51 GMT

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

Re: How to replace missing values within household level data

data_null__ — Fri, 18 Feb 2011 23:21:14 GMT

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]

Re: How to replace missing values within household level data

Vasile01 — Sat, 19 Feb 2011 02:53:27 GMT

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