is Hash Table many keys to one key match doable?

[happy_sas_kitty]

I'm trying to match keys 'A' and 'B' both to key 'C'. I wonder if this is doable in hash table.

Simplified version of this problem:

key: Location, Gender

Value: Pet

data dict;
infile datalines;
input Location$ Gender $ Pet $;
datalines;
GreenBay M Dog
Madison F Badger
Milwaukie B Cat
;
run;
data have;
infile datalines;
input Subject$ Location$ Gender$;
datalines;
1 GreenBay M
2 Madison F
3 Milwaukie M 
4 Milwaukie F
;
run;
data want;
infile datalines;
input Subject$ Location$ Gender$ Pet$;
datalines;
1 GreenBay M Dog
2 Madison F Badger
3 Milwaukie M Cat
4 Milwaukie F Cat
;
run;

Using 'dict' dataset as hashtable, I want the result 'want' dataset.

Obviously, F and M in 'have' dataset should both match to B (means both) in dict dataset.

Can we do something to the hash table to make this happen? 

I can solve this in some non-hashtable ways:

1. alter work.dict so that if we see a B, we output(copy) it into 2 rows with F and M

2. proc sql left join.

In reality, both 'have' dataset and 'dict' dataset have about 1000 rows so I really don't want to use the methods above.

[PeterClemmensen]

Shouldn't the variable Subject be the primary lookup variable here and not gender? Otherwise all obs in target would match all obs in dict where gender = "B"? I assume your actual data is a bit bigger than the posted 🙂

What if your dict table looks like this? What would your desired result look like?

data dict;
infile datalines;
input Subject $ Gender $ Pet $;
datalines;
1 B CAT
3 B DOG
;

[happy_sas_kitty]

Yes you are right. I fixed the datasets. You can now run them again.

[PhilC]

You say you don't want to alter work.dict, and you probably don't want to make a copy either.  What about a view?

data dict_view/view=dict_view;
  set dict;
  if Gender="B" then do;
    Gender="M"; output;
    Gender="F"; output;
  end;
  output;
run;

data try;
  if 0 then set dict_view;
  declare hash dict(dataset:"dict_view", multidata:"y");
  rc= dict.defineKey("SUBJECT","Gender")
     +dict.defineData("Pet")
     +dict.defineDone();
/*...*/
run;

[Tom]

I cannot make heads or tails of what you are trying to do.  But if you want both M and F to match to a hash key of B then you could create a format that maps M and F to B then use the formatted value as they in the hash.FIND() method call.

proc format ;
value $genderkey 'M','F'='B';
run;
...
rc=h.find(key: put(gender,$genderkey.));
...

[happy_sas_kitty]

sorry for the confusion. Yes if that was what I wanted then your answer would be correct. However the logic is, I want to match F to F, M to M , if dict has no F nor M but a key B then I would want F and M match to B.

[Tom]

The reply button should be below the right hand lower corner of the message.

[happy_sas_kitty]

Thank you for the reply but yesterday somehow there were no "quick reply" and "reply" buttons . There were only 'Likes' and "add Tags". I refreshed my chrome many times and nothing changed. However today these buttons magically showed up. No idea what happened.

[PeterClemmensen · Accepted solution]

Here is how I interpret your request. Obviously, you want M to match M and F to match F in your lookup. However, if a match if not found, you want both M and F to match B.

The code below first looks up in dict using the PDV "host" value. If a match is not found, we simply do a explicit lookup for "B". 

Let me know if this works for you 🙂

data want(drop = rc);

   if _N_ = 1 then do;
      dcl hash h(dataset : 'dict');
      h.definekey('Gender');
      h.definedata('Pet');
      h.definedone();
   end;

   set target;
   if 0 then set dict;

   if h.find() then rc = h.find(key : 'B');

run;

Result:

Subject Gender Pet 
1       M      CAT 
2       F      CAT 

[happy_sas_kitty]

This is indeed what I want if we can't do anything to the hashtable keys.
Would you mind explaining how the h.find(key:'B') works?
I read the SAS website but they did something like:
h.add(key:'B', data:'Cat')
h.find(key:'B')
How did you do it without adding data?

[PeterClemmensen]

Sure thing 🙂

The Hash Object Find Method searches for a key value in the hash object and updates the data variables if the search is succesful.

The Find Method can be called either implicitly or explixitly.

The implicit call h.find() (notice there is nothing in the parentheses) searches using the value(s) in the PDV host variables. In this case we search for "M" and "F" respectively because they are in the PDV in obs 1 and 2.

The explixit call allows us to specify the key to search for directly in the parentheses, taking precedence over the PDV value.

Hope this makes sense.

[PhilC]

Sorry you're having trouble with the website.

Is there a way for us to define the ordering and the equality of character values so we can alter the behavior of the HASH object or the MERGE statement? I'd say No, not in my experience have I encountered a method to make the characters B equate to F and M.

If there was a way to make B equal F and B equal M by changing a keymap you'd make F equal M.

I've coded using C++, there you can "overload the operators". Coding any special case you dream of Using '=', '<' and '>'. I know nothing of this existing in SAS.

Knowing computer science, there's probably a way, so asking the question is a worthy exercise. And I hope someone shows us how to do this here.

[happy_sas_kitty]

I agree with you. I came from Java and Python. I know that this is against the rules of a hashtable. For example in Java, the key of a key - value/values pair should be unique. As you just said in C++ "Overload the Operators", in java we can do a work around in the same way. We can define our own 'Comparators' where we can say that F and M are both equal to B.
I wonder if SAS can do the same thing.