## Connecting different observations based on the values that two different variables take on

Occasional Contributor
Posts: 11

# Connecting different observations based on the values that two different variables take on

This question is a bit vague but I need to know how I can go about doing something like this in SAS and even if it is possible.

Here is the scenario: I am trying to connect mothers to there children. I will have families identified.

Therefore, I will have family A, with family members 1, 2, 3, 4, 5. For each member, I will know which members are mothers, and which are kids. I have to connect mothers to their children. Children have a variable equal to the ID number of their mother. How can I connect them? It would require SAS to look at a child's connection number, within a family, and link it to his/her mom based on her ID number. This would require me to "jump" accross the members of a family looking to match these two variables. I do not know where to look  regarding SAS to learn how to do this. Thank you for your time.

Super User
Posts: 24,012

## Re: Connecting different observations based on the values that two different variables take on

It depends on your data structure. Either a data step merge or PROC SQL will likely be required.

The best way to answer this would for you to post sample data and what you want as output and we can either direct you towards a solution or assist in coding a solution. Please post what you've tried as well.

Super User
Posts: 10,850

## Re: Connecting different observations based on the values that two different variables take on

Better post some data and output to explain what you want .

Here is an eample.

``````
/*************************Belong to the same household*******************************/

data have;
infile cards ;
input from \$  to \$ ;
cards;
1     2
1     3
4     5
5     2
9     4
6     7
8     7
;
run;
data full;
set have end=last;
if _n_ eq 1 then do;
declare hash h();
h.definekey('node');
h.definedata('node');
h.definedone();
end;
output;
node=from; h.replace();
from=to; to=node;
output;
node=from; h.replace();
if last then h.output(dataset:'node');
drop node;
run;

data want(keep=node household);
declare hash ha(ordered:'a');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('last');
ha.definedone();
declare hash _ha(hashexp: 20);
_ha.definekey('key');
_ha.definedone();

if 0 then set full;
declare hash from_to(dataset:'full(where=(from is not missing and to is not missing))',hashexp:20,multidata:'y');
from_to.definekey('from');
from_to.definedata('to');
from_to.definedone();

if 0 then set node;
declare hash no(dataset:'node');
declare hiter hi_no('no');
no.definekey('node');
no.definedata('node');
no.definedone();

do while(hi_no.next()=0);
household+1; output;
count=1;
rc=hi.first();
do while(rc=0);
from=last;rx=from_to.find();
do while(rx=0);
key=to;ry=_ha.check();
if ry ne 0 then do;
node=to;output;rr=no.remove(key:node);
count+1;
end;
rx=from_to.find_next();
end;
rc=hi.next();
end;
ha.clear();_ha.clear();
end;
stop;
run;
``````
Discussion stats
• 2 replies
• 152 views
• 0 likes
• 3 in conversation