topic Re: How to compare two variables across observations? in New SAS User

How to compare two variables across observations?

newfee — Wed, 30 Sep 2020 06:10:37 GMT

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.

Re: How to compare two variables across observations?

Amir — Wed, 30 Sep 2020 06:43:22 GMT

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.

Re: How to compare two variables across observations?

newfee — Wed, 30 Sep 2020 08:52:03 GMT

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).

Re: How to compare two variables across observations?

Ksharp — Thu, 01 Oct 2020 14:01:21 GMT

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;

Re: How to compare two variables across observations?

newfee — Thu, 08 Oct 2020 05:06:36 GMT

Thank you so much!

Re: How to compare two variables across observations?

newfee — Thu, 08 Oct 2020 05:10:38 GMT

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

Re: How to compare two variables across observations?

Ksharp — Thu, 08 Oct 2020 11:23:25 GMT

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;