Create all possible combination of one variable by group of another variable

[mich]

Hi,

I have two columns, the first is the group, the second is the name of an entity withing that group.

Group   Entity

A           Prod1

A           Prod2

A           Prod3

B           Prod2

B           Prod4

B...........

I need to transform it to a dataset with 3 columns, the first is the group and the second and the third contains all possible combinations of Entities(2 by 2) within that group

Group   Entity1   Entity2

A           Prod1     Prod2

A           Prod1     Prod3

A           Prod2     Prod3

B           Prod2     Prod4

B           Prod2 .............

B...........

Thank you,

Michel

[RW9]

Would something like;

data have;

  input group $ Entity $;

datalines;

A           Prod1

A           Prod2

A           Prod3

B           Prod2

B           Prod4

;

run;

proc sql;

  create table WANT as

  select  A.*,

          B.ENTITY as ENTITY2

  from    HAVE A

  left join HAVE B

  on      A.GROUP=B.GROUP

  and     input(strip(tranwrd(B.ENTITY,"Prod","")),best.) > input(strip(tranwrd(A.ENTITY,"Prod","")),best.)

  where   ENTITY2 is not null;

quit;

[Patrick]

Or this way:

data have;

  input group $ Entity $;

datalines;

A Prod1

A Prod2

A Prod3

B Prod2

B Prod4

;

run;

proc sql;

  create table WANT as

  select  A.*,

          B.ENTITY as ENTITY2

  from    HAVE A, Have B

  where a.group=b.group and a.entity<b.entity

  ;

quit;

[Loko]

Hello,

Hash will also do it:

data have;
input Group $  Entity $;
datalines;
A Prod1
A Prod2
A Prod3
B Prod2
B Prod4
;
run;

data want;

if _n_=1 then do;
declare hash h (dataset:"have(rename=entity=entity2)", multidata:"Y");
h.definekey("group");
h.definedata("entity2");
h.definedone();
end;

set have;
length entity2 $ 10;

rc=h.find(key:group);

do while(rc=0);
if entity gt entity2 then output;
if rc ne 0 then call missing(entity2);
rc=h.find_next();

end;

drop rc;
run;

[Ksharp]

Or this way:

data have;

  input group $ Entity $;

datalines;

A Prod1

A Prod2

A Prod3

B Prod2

B Prod4

;

run;

data want;

set have;

by group;

length e1 e2 $ 100;

array x{99999} $ 100 _temporary_;

if first.group then n=0;

n+1;x{n}=Entity;

if last.group then do;

  do i=1 to n-1;

   do j=i+1 to n;

    e1=x{i};e2=x{j};output;

   end;

  end;

end;

drop n Entity i j;

run;

Xia Keshan

[mich]

thanks Xia, very helpful

thanks all

[BellaLuna]

This is very helpful. Thank you.