topic How to join based on if key has 1 or 0 in other fields in SAS Programming

How to join based on if key has 1 or 0 in other fields

mhoward2 — Wed, 22 Sep 2021 19:48:51 GMT

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!

Re: How to join based on if key has 1 or 0 in other fields

Reeza — Wed, 22 Sep 2021 19:44:17 GMT

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

Re: How to join based on if key has 1 or 0 in other fields

mhoward2 — Wed, 22 Sep 2021 19:51:52 GMT

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.

Re: How to join based on if key has 1 or 0 in other fields

Reeza — Wed, 22 Sep 2021 19:59:28 GMT

Then transpose Table1 and merge it with table2?

Re: How to join based on if key has 1 or 0 in other fields

mhoward2 — Wed, 22 Sep 2021 20:12:16 GMT

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?

Re: How to join based on if key has 1 or 0 in other fields

Astounding — Wed, 22 Sep 2021 20:33:16 GMT

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.

Re: How to join based on if key has 1 or 0 in other fields

Kurt_Bremser — Wed, 22 Sep 2021 20:39:47 GMT

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.

Re: How to join based on if key has 1 or 0 in other fields

novinosrin — Wed, 22 Sep 2021 20:54:20 GMT


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;

Re: How to join based on if key has 1 or 0 in other fields

Ksharp — Thu, 23 Sep 2021 12:44:35 GMT

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;

Re: How to join based on if key has 1 or 0 in other fields

mhoward2 — Thu, 23 Sep 2021 13:24:29 GMT

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?

Re: How to join based on if key has 1 or 0 in other fields

Ksharp — Thu, 23 Sep 2021 13:31:47 GMT

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;

Re: How to join based on if key has 1 or 0 in other fields

mhoward2 — Thu, 23 Sep 2021 15:42:24 GMT

Thank you so much this was perfect! I very much appreciate you helping me out!