Quartz | Level 8

## Create a new variable based on other variables

There is this dataset. Var1 shows the place within a group. Var2 shows the ID to which the member is related to.

site ID var1 var2
1    11    2       .
1    12   1      11
2    21   2       .
2    22   1      21
2    31   2       .
2    33   1     31

We need to create a variable that shows that two IDs belong to a group.

site ID var1 var2 group
1    11    2       .      1
1    12   1      11     1
2    21   2       .       2
2    22   1      21     2
2    31   2       .       3
2    33   1     31      3

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## Re: Create a new variable based on other variables

I still think there is a piece of logic you haven't explained, but based upon what you have said, this works:

``````data want;
set have;
group=1+floor((_n_-1)/2);
run;``````

I contend something hasn't been explained, because the above solution doesn't even look at the value of var1 and var2.

--
Paige Miller
11 REPLIES 11
Diamond | Level 26

## Re: Create a new variable based on other variables

Please explain the logic used here to derive variable GROUP.

--
Paige Miller
Quartz | Level 8

## Re: Create a new variable based on other variables

group indicates that ID 11 and ID 12 are associated. var2 indicates that ID 12 is related to ID 11 (value 11).

Diamond | Level 26

## Re: Create a new variable based on other variables

is VAR1 always 2 in the first record and VAR1 always 1 in the second record? Is VAR2 always present when var1=1 and missing when var1^=1? Are there ever more than 2 records in each GROUP?

--
Paige Miller
Quartz | Level 8

## Re: Create a new variable based on other variables

Yes to the first two questions. No, only 2 records per group.
Diamond | Level 26

## Re: Create a new variable based on other variables

I still think there is a piece of logic you haven't explained, but based upon what you have said, this works:

``````data want;
set have;
group=1+floor((_n_-1)/2);
run;``````

I contend something hasn't been explained, because the above solution doesn't even look at the value of var1 and var2.

--
Paige Miller
Quartz | Level 8

## Re: Create a new variable based on other variables

Thanks. We need for ID 11 and ID 12 to belong to the same unique group. ID 12 shows that it is related to ID 11 by showing in var2 that 11 is the ID it is attached to. var1 shows that ID 11 is number 1 in the list and ID 12 is number 2.
Diamond | Level 26

## Re: Create a new variable based on other variables

Yes, I think I understand that, but what is wrong with my solution that doesn't use VAR1 or VAR2? I get the proper values of GROUP.

--
Paige Miller
Quartz | Level 8

## Re: Create a new variable based on other variables

Your code worked perfectly. I was just wondering if a proc sort will be necessary in order not to mess up the pairings? Since the group variable is based on observation number?
Diamond | Level 26

## Re: Create a new variable based on other variables

What is not sorted properly?

--
Paige Miller
Quartz | Level 8

## Re: Create a new variable based on other variables

I was just wondering if there should be prior step but it works great. Thanks!

Super User

## Re: Create a new variable based on other variables

``````data x;
infile cards expandtabs;
input site ID var1 var2;
from=catx('|',site,id);
to=catx('|',site,var2);
cards;
1    11    2       .
1    12   1      11
2    21   2       .
2    22   1      21
2    31   2       .
2    33   1     31
;

data have;
set x;
if not missing(id) and not missing(var2);
keep from to;
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: 16);
_ha.definekey('key');
_ha.definedone();

if 0 then set full;
declare hash from_to(dataset:'full',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;

proc sql;
create table final_want as
select a.site,a.id,a.var1,a.var2,b.household as group
from x as a left join want as b
on catx('|',a.site,a.id)=b.node
order by site,id;
quit;``````
Discussion stats
• 11 replies
• 373 views
• 0 likes
• 3 in conversation