topic Re: Create all possible combination of one variable by group of another variable in SAS Procedures

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

mich — Mon, 20 Apr 2015 07:49:44 GMT

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

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

RW9 — Mon, 20 Apr 2015 08:25:15 GMT

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;

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

Patrick — Mon, 20 Apr 2015 10:15:56 GMT

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;

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

Loko — Mon, 20 Apr 2015 11:59:51 GMT

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;

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

Ksharp — Mon, 20 Apr 2015 12:38:13 GMT

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;

drop n Entity i j;

run;

Xia Keshan

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

mich — Tue, 21 Apr 2015 06:22:25 GMT

thanks Xia, very helpful

thanks all

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

BellaLuna — Sat, 25 Jun 2016 19:38:20 GMT

This is very helpful. Thank you.