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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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