Hi @JackoNewbie
You can create a macro program to do this, with two mandatory arguments (sport n°1 and sport n°2) and an optional one which is the output dataset name.
One way to avoid many macro call is two automatize that through call execute.
Best,
data research;
length name $10 Value 8. Preferred_Sports $100;
infile datalines dsd;
input Name $ Value Preferred_Sports $;
datalines;
Jude,32,"Football,Basketball,Tennis,Darts"
Jack,39,"Hockey,Darts,Judo,Football"
Harry,20,"Basketball,Tennis,Hockey"
Tom,8,"Badminton,Tennis,Darts,Basketball"
Boris,14,"Badminton,Hockey,Judo"
Pier,18,"Judo"
Michael,12,"Darts,Polo,Tennis"
Roger,49,"Hockey,Darts,Judo"
Felix,1,"Football,Basketball,Tennis,Darts"
;
data interest;
input Sport:$20.;
datalines;
Football
Basketball
Darts
;
%macro sql_couple (couple1, couple2, dataset=summary);
proc sql;
create table &dataset. as
select
count (distinct name) as people,
sum (value) as total_value
from research
where Preferred_Sports contains "&couple1."
and Preferred_Sports contains "&couple2.";
quit;
proc print;
%mend;
%sql_couple (Football, Basketball)
/***** To create a dataset for all combinations (avoid many macro calls)*/
/* Create a list of all couples */
proc transpose data=interest out=interest_tr (drop=_:) prefix=sport;
var sport;
run;
data interest_couples;
set interest_tr;
length couple $ 200;
array f(*) sport:;
do i=1 to dim(f);
do j=i to dim(f);
if f(i) ne f(j) then do;
couple1 = f(i);
couple2 = f(j);
couple = catx(", ",f(i), f(j));
count+1;
dst=cats('table',count);
if not missing(couple) then output;
end;
end;
end;
run;
proc sort data=interest_couples out=interest_couples_list (keep=couple1 couple2 dst) nodupkey;
by couple;
run;
/* Call execute */
data _null_;
set interest_couples_list;
call execute (cats('%sql_couple (',couple1,',',couple2,', dataset=',dst,')'));
run;
... View more
