Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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

Capture.PNG

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:

Capture2.PNG

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.


Accepted Solutions
Solution
‎05-22-2015 09:44 PM
Respected Advisor
Posts: 4,654

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

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)

PG

PG

View solution in original post


All Replies
Solution
‎05-22-2015 09:44 PM
Respected Advisor
Posts: 4,654

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

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)

PG

PG
Trusted Advisor
Posts: 1,204

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

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;

Respected Advisor
Posts: 4,654

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

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;

PG

PG
Super User
Posts: 9,682

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

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

Respected Advisor
Posts: 4,654

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

   

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;

PG

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 341 views
  • 6 likes
  • 4 in conversation