Calcite | Level 5

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

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

6 REPLIES 6
Diamond | Level 26

## Re: Create all possible combination of one variable by group of another variable

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;

Opal | Level 21

## Re: Create all possible combination of one variable by group of another variable

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;

Barite | Level 11

## Re: Create all possible combination of one variable by group of another variable

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;

Super User

## Re: Create all possible combination of one variable by group of another variable

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

Calcite | Level 5