I am trying to create a table with all the possible intersections of 4 tables in the same table with for example a colum that indicates the reference tables.
A with B
A with C
...
A with B with C with D
I can easily achive this but i dont want to create 15 tables and append in the end i would prefere to do this with a macro.
Is it possible?
Thanks for the help i will leave the proc sql that i use to create one of the intersects.
proc sql;
create table A as
select A.*,CAT(A.ID,' ',B.ID)AS MASTER_ID
from
work1 as A inner join
work2 as B on ...
run;
Since you plan on appending all the possible data sets back together again, you would be much better off with a single DATA step and a single variable to indicate matches/mismatches. Assuming that your data sets are sorted first:
data want;
merge a (in=in1) b (in=in2) c (in=in3) d (in=in4);
by id;
match = cats(of in1-in4);
run;
You could probably solve it with a macro, but i can't see that it would be simple...
Perhaps there's another way?
Describe your requirement in more detail, like
Yea in structure they are identical just have an ID and a description of the group and each table represents different groups, the final result is a table that have the Count of ID's for each possible combination so in the end i would have a table like
Combination | Count |
A&B | 100 |
A&C | 200 |
A&B&C | 50 |
… |
Since you plan on appending all the possible data sets back together again, you would be much better off with a single DATA step and a single variable to indicate matches/mismatches. Assuming that your data sets are sorted first:
data want;
merge a (in=in1) b (in=in2) c (in=in3) d (in=in4);
by id;
match = cats(of in1-in4);
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.