So I apologize I don't know exactly what this is called but I'll show you what I have and want, I just don't know how to do it. Note, I have tables 1 and 2, I want table 3.
So Table 1 shows which have which modules (AA, BB, CC being the modules, if it shows a 1 then they have it, 0 indicates they don't have it.)
Key | AA | BB | CC |
1001 | 1 | 0 | 0 |
1002 | 1 | 1 | 0 |
1003 | 0 | 1 | 1 |
1004 | 1 | 0 | 1 |
Table 2 shows a list of just some possible combinations of Key and Module, but the Mods are now in a single column.
Key | Mod |
1001 | AA |
1001 | BB |
1002 | BB |
1003 | BB |
1003 | CC |
1004 | CC |
1004 | AA |
So what I want to do is take Table 2 and filter it down to only where that Key has that Module. So the table I want would look like this:
Key | Mod |
1001 | AA |
1002 | BB |
1003 | BB |
1004 | CC |
1004 | AA |
Does this make sense? I feel like it should be possible but its touch since in Table 1 the Mods are field names where as in Table 2 they are the values in the table. Any and all help very much appreciated!
Thanks!
data table1; input Key AA BB CC; cards; 1001 1 0 0 1002 1 1 0 1003 0 1 1 1004 1 0 1 ; data table2; input Key Mod $; cards; 1001 AA 1001 BB 1002 BB 1003 BB 1003 CC 1004 CC 1004 AA ; data temp; set table1; array x{*} AA--CC; do i=1 to dim(x); if x{i}=1 then do;mod=vname(x{i});output;end; end; keep key mod; run; proc sql; create table want as select * from table2 intersect select * from temp; quit;
PROC TRANSPOSE + WHERE for first step - not sure I understand the logic of the second step.
proc transpose data=have out=want (where=(col1 ne 0) rename=_name_ = MOD);
by KEY;
var AA BB CC;
run;
General idea, untested (my data set options may be formatted uncorrectly).
Thanks for stopping in. Sorry I wasnt exactly clear. I have tables 1 and 2 and want to join them in a way to get table 3. As you can see, table 2 has entries where a certain Key has an associated Mod, but in table 1 they show a 0 for that Key/Mod. So table 3 would cut that entry out. Does that makes sense? Table 3 is just a version of table 2 where only associated Key/Mod entries have a 1 in table 1.
Hmmm I'm not sure how that would work. If I did that wouldn't I just be making the Keys into field names instead of the Mods? How will that help me join on Key and Mod?
Transposing table 1 will turn the variable names into values (in _NAME_ which is renamed to MOD), so you can then join on these values with MOD in table2.
I can't code it for you (others can), but I can give you a reasonable approach.
In a DATA step, load Table 2 into a hash table. Then examine each "1" value in Table 1, (calculating MOD along the way), and output when looking in the hash table finds a match.
data table1;
input Key AA BB CC;
cards;
1001 1 0 0
1002 1 1 0
1003 0 1 1
1004 1 0 1
;
data table2;
input Key Mod $;
cards;
1001 AA
1001 BB
1002 BB
1003 BB
1003 CC
1004 CC
1004 AA
;
data want;
merge table2 table1;
by key;
if input(vvaluex(mod),32.);
keep key mod;
run;
data table1; input Key AA BB CC; cards; 1001 1 0 0 1002 1 1 0 1003 0 1 1 1004 1 0 1 ; data table2; input Key Mod $; cards; 1001 AA 1001 BB 1002 BB 1003 BB 1003 CC 1004 CC 1004 AA ; data temp; set table1; array x{*} AA--CC; do i=1 to dim(x); if x{i}=1 then do;mod=vname(x{i});output;end; end; keep key mod; run; proc sql; create table want as select * from table2 intersect select * from temp; quit;
Hi! Thanks for helping out! How would I replace the part of your sample code that says "AA--CC" with the actual mods in my data set? Ideally it would be dynamic so the amount of mods and what they are named can change. So is there a different way to populate the array by saying "all other field names" instead of specifying them exactly?
OK. Try this :
data temp; if 0 then set table1(drop=key); array x{*} _numeric_; set table1; do i=1 to dim(x); if x{i}=1 then do;mod=vname(x{i});output;end; end; keep key mod; run;
Thank you so much this was perfect! I very much appreciate you helping me out!
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 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.