topic Re: list of Census house records, where 2 or more occupants are related to the primary head of household in SAS Procedures

list of Census house records, where 2 or more occupants are related to the primary head of household

Bautista — Sat, 23 May 2015 00:37:53 GMT

I have a Census data file of persons who occupy various houses. Sample data shown below.

In every house, there is always a person numbered "1" who is the primary respondent to the Census survey (known as the head of household). Sometimes, this person lives alone. Or with relatives, or nonrelatives, or a combination of both. I want to create a list of the HouseNumbers that correspond to houses in which the head of household (person 1) is living with at least 2 of his or her relatives. So using the above example, the output would be:

House #128 would not be included, because person 1 ("self", also known as the respondent) is only related to one other person in house #128. Also, house #136 and house #149 would not be included because the respondent lives alone (no relatives or nonrelatives).

In my output table, I only want two variables: ID and House.

Re: list of Census house records, where 2 or more occupants are related to the primary head of household

PGStats — Sat, 23 May 2015 01:44:02 GMT

If you can't assume that the primary respondent in a house has the smallest ID then you need:

proc sql;

create table SelfWithTwoRelatives as

select b.id, b.house

from (

select house

from myData

group by house

having sum(RelationshipToRespondent = "Relative") >= 2) as a inner join

myData as b on a.house=b.house

where b.RelationshipToRespondent="Self";

quit;

(Couldn't test against your data which can't be imported into SAS)

Re: list of Census house records, where 2 or more occupants are related to the primary head of household

stat_sas — Sat, 23 May 2015 02:09:22 GMT

proc sql;

create table want as

select min(id) as id,a.house from have a

left join (select house,count(*) as freq from have

where relationshiptorespondent='Relative' group by house) b

on a.house=b.house

group by a.house

having freq>=2;

quit;

proc print data=want;

run;

Re: list of Census house records, where 2 or more occupants are related to the primary head of household

PGStats — Sat, 23 May 2015 02:30:15 GMT

If you can assume that MIN(id) is Self for every house then you don't need a join :

proc sql;

create table SelfWithTwoRelatives as

select min(id) as id, house

from myData

group by house

having sum(RelationshipToRespondent = "Relative") >= 2;

quit;

Re: list of Census house records, where 2 or more occupants are related to the primary head of household

Ksharp — Sat, 23 May 2015 06:11:47 GMT

PG,

HAVING also can filter the obs ,not just the group .

proc sql;

create table SelfWithTwoRelatives as

select *

from myData

group by house

having sum(RelationshipToRespondent = "Relative") >= 2 and PersonNumber=1;

quit;

Xia Keshan

Re: list of Census house records, where 2 or more occupants are related to the primary head of household

PGStats — Sat, 23 May 2015 20:14:48 GMT

Great! This query can even be done without a join and without remerging (implicit join) :

proc sql;

create table SelfWithTwoRelatives as

select

max(case when RelationshipToRespondent="Self" then id else . end) as id,

house

from myData

group by house

having sum(RelationshipToRespondent = "Relative") >= 2;

quit;