Collapsing multiple equalities into one line

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Collapsing multiple equalities into one line

I have a SAS Datasets with multiple equalities: i.e. A=C, B=C etc. and I would like to make concatenate it into one line to A=B=C.

 

For example, IDs in 1 and 2 represent equality in the following dataset:

ID1, ID2;

1,3;

2,3;

1,4;

 

I would like to convert this into:

 

ID1, ID2, ID3 ID4;

1, 2, 3, 4;

 

Would there be a good algorithm to do this?


Accepted Solutions
Solution
‎06-28-2016 09:35 AM
Grand Advisor
Posts: 9,567

Re: Collapsing multiple equalities into one line

It sounds like you want search a tree and find out who belong to the same sub-graph .

 

 


data have;
infile cards ;
input from   to  ;
cards;
1 3 
2 3
1 4
;
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;



proc transpose data=want out=final_want(drop=_name_) prefix=ID;
by household;
var node;
run;

View solution in original post


All Replies
Solution
‎06-28-2016 09:35 AM
Grand Advisor
Posts: 9,567

Re: Collapsing multiple equalities into one line

It sounds like you want search a tree and find out who belong to the same sub-graph .

 

 


data have;
infile cards ;
input from   to  ;
cards;
1 3 
2 3
1 4
;
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;



proc transpose data=want out=final_want(drop=_name_) prefix=ID;
by household;
var node;
run;

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 170 views
  • 1 like
  • 2 in conversation