Hash Join -- Cannot pick and choose columns to join

[Viffer]

Hello, Everyone,

I have been told great things about the SAS Hash Join and am trying to implement it in a project with a very large dataset (WORK.MASTER) that I would like to join to a smaller dataset (WORK.HASH_CLASS).  I can get the code to execute and join a table; however, SAS is joining ALL columns of the second table to my master table, even though I only desire and specify one.  Please refer to my code as follows:

My goal is to simply add the "CLASS_REMAPPED" field from the WORK.HASH_CLASS table to the WORK.MASTER table; however, SAS proceeds to join *all* fields from WORK.HASH_CLASS to WORK.MASTER, even though I only specified "CLASS_REMAPPED" in the .defineData property.  Can anyone see what I'm doing wrong?

Thanks!

[Haikuo]

Hi, From what I can see, you are only add in one variable "CLASS_REMAPPED" in the join. However, since when you define hash, you did have prepared all variables from 3 tables (which you need 1 for your purpose) in PDV, so the other variables will remain missing, but they are there. If you don't want those empty variables, then restrict it by using data set options (keep=), so you will only have those needed variables in your PDV.

Haikuo

[Viffer]

Sorry -- please ignore the WORK.HASH_ABVRG table in my set statement; that's a remanant of my original code (joining to multiple tables), but I created this simplified example to illustrate my problem.

Could you please tell me what you mean by "PDV"?  Are you saying that I must ensure that the table I'm joinging to only has the key and the desired mapping values prior to doing the hash join?  If so, then what is the purpose of the following statement?

ClassMap.defineData('CLASS_REMAPPED')

Thanks!

[Haikuo]

Can you please post your code again in text instead of screen shot so I can modify it? It will be easier for me to explain and for you to understand when we have two sets of code side by side.

Haikuo

[Ksharp]

Because you have used statement "if 0 then set  WORK.HASH_CLASS ;" which brought all the variables in it into your result dataset . Try to use "length CLASS_REMAPPED 8;" if it is numeric or "length CLASS_REMAPPED $ 40 ;" if it is character.

Ksharp