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
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;
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;
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;
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
thanks Xia, very helpful
thanks all
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.