BookmarkSubscribeRSS Feed
mich
Calcite | Level 5

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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
Barite | Level 11

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
Super User

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
Calcite | Level 5

thanks Xia, very helpful

thanks all

BellaLuna
Fluorite | Level 6
This is very helpful. Thank you.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3402 views
  • 2 likes
  • 6 in conversation