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
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.
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.