How to compare two variables across observations?

[newfee]

Hi,

I have a Data Set looking like this:

id  group

1   A

2   B

2   A

3   B

and want to have something like this:

id   group   group_new

1    A          A

2    B          A

2    A          A

3    B          A

Unfortunately, id's belonging to one group are not consecutive observations.

[Amir]

Hi,

Some points to consider to help get a solution are:

What are the rules / logic for deciding what value should be in the group_new variable?

At the moment the example in the question shows it is always "A".

Should it always be "A" or can it ever change? If it can change then under what circumstances and what should the different value be?

I recommend you edit the original question to further clarify.

Kind regards

Amir.

Edited to add recommendation.

[newfee]

I tried to simplify the data. The "have"-table actually looks more like this:

id   group

1      A

2      B

2      A

3      C

4      C

5      D

6      E  

7      B

and what I want is:

id    group    group_new

1      A             A

2      B             A

2      A             A

3      C            C

4      C            C

5      D            D

6      E            E

7      B            A

The general rule is that the same id can have only one group, not two (in "have" it is never more than two). So id 2 must either have group A or group B (id 1 and 2 both have group A, id 2 and 7 are in group B, so id 2 is in group A and B and I want to "overwrite" B with A for id 2 and id 7).

[Ksharp · Accepted solution]

Why overwrite B with A not A with B ?

data have;
infile cards ;
input from $  to $ ;
cards;
1      A
2      B
2      A
3      C
4      C
5      D
6      E  
7      B
;
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;
 key=node;_ha.add();
 last=node;ha.add();
 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);
       key=to;_ha.add();
       count+1;
       last=to;ha.add();
      end;
      rx=from_to.find_next();
   end;
   rc=hi.next();
end;
ha.clear();_ha.clear();
end;
stop;
run;

data want;
 set want;
 by household;
 length new_group $ 80;
 retain new_group;
 if first.household then new_group=node;
run;

data final_want;
 if _n_=1 then do;
   if 0 then set want;
   declare hash h(dataset:'want');
   h.definekey('node');
   h.definedata('new_group');
   h.definedone();
 end;
set have;
h.find(key:from);
drop node household;
run;

[newfee]

Thank you so much!

[newfee]

A could be overwritten with B, too. It doesn't matter. I just need A and B to be the same new_group.

[Ksharp]

If you have SAS/OR could try this code proposed by @PGStats 

/* Same code as SAS/OR */
proc optnet data_links=have out_nodes=want GRAPH_DIRECTION=UNDIRECTED;
data_links_var from=from to=to;
concomp;
run;