Looping through a Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Looping through a Dataset

I'm trying to find parents with children.  So in this example dataset below, is there a way to loop through a household and see if they have a mom_num or dad_num and match that to the pernum in the household?

 

So in this example below, for household id 001, the daughter has a mom_num of 01 which matches the head.  So in this case, I'd want to have a parent_flag to indicate that the head in household ID 001 is a parent with a child in the household.  Is there a way to do this?

 

HH ID    Relationship PERNUM  SPOUSE_NUM    MOM_NUM       DAD_NUM

001Head           01          00                    00                    00
001Daughter02000100
002Son-in-law03040000
002Daughter04030100
002Grandchild05000403
003Boarder06070000
003Boarder07060000

Accepted Solutions
Solution
‎11-08-2016 10:31 AM
Grand Advisor
Posts: 9,584

Re: Looping through a Dataset

data have;
infile cards expandtabs truncover;
input HHID    Relationship : $20. PERNUM  SPOUSE_NUM    MOM_NUM       DAD_NUM;
cards;
001	Head  	01 	00   	00    	00
001	Daughter	02	00	01	00
002	Son-in-law	03	04	00	00
002	Daughter	04	03	01	00
002	Grandchild	05	00	04	03
003	Boarder	06	07	00	00
003	Boarder	07	06	00	00
;
run;
proc sql;
select *,case when pernum in
(select mom_num from have where hhid=a.hhid union
 select dad_num from have where hhid=a.hhid) then 1
 else 0 end as flag 
 from have as a;
quit;

View solution in original post


All Replies
Grand Advisor
Posts: 10,215

Re: Looping through a Dataset

It may help to show a more complete example of the result for the example data provided. The example should have at least one each type of match you need processed.

Contributor
Posts: 60

Re: Looping through a Dataset

So I'd expect to see a parent_flag like this - where 1 is they are the parent with child, 0 they are not a parent with a child in the household.  Does this help?

 

HH IDRelationshipPernumspouse_nummom_numdad_numParent_flag
001Head           01          00   00     001
001Daughter020001000
002Son-in-law030400001
002Daughter040301001
002Grandchild050004030
003Boarder060700000
003Boarder070600000
Solution
‎11-08-2016 10:31 AM
Grand Advisor
Posts: 9,584

Re: Looping through a Dataset

data have;
infile cards expandtabs truncover;
input HHID    Relationship : $20. PERNUM  SPOUSE_NUM    MOM_NUM       DAD_NUM;
cards;
001	Head  	01 	00   	00    	00
001	Daughter	02	00	01	00
002	Son-in-law	03	04	00	00
002	Daughter	04	03	01	00
002	Grandchild	05	00	04	03
003	Boarder	06	07	00	00
003	Boarder	07	06	00	00
;
run;
proc sql;
select *,case when pernum in
(select mom_num from have where hhid=a.hhid union
 select dad_num from have where hhid=a.hhid) then 1
 else 0 end as flag 
 from have as a;
quit;

Contributor
Posts: 60

Re: Looping through a Dataset

Thank you so much! Worked like a charm!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 236 views
  • 0 likes
  • 3 in conversation