topic Hash Merge Using Explicit Key Fields in SAS Programming

Hash Merge Using Explicit Key Fields

theponcer — Tue, 16 Apr 2019 21:59:35 GMT

I am trying to hash merge using the key: logic to define which field in the base table I want to use to join. The first two keys are named the exact same in the base and join tables, but the third field differs between the two tables. However, I keep getting errors! Here is my base code:

data test_table ;
set work.test;
length Indicator $5;
if 0 then set toodle ;


if _n_=1 then do;

		dcl hash one (dataset:'toodle');
			one.definekey ('Product','Status','Indicator_1');
/*			one.definekey ('Product','Status');*/
			one.definedata('core_1');
			one.definedone();


rc_1 = one.find(key:'Product'n,key:Status,key:'Indicator');
run;

I have tested using only the commented out key definition - this works. However, when I add the "Indicator_1" field, I get the following errors:

ERROR: Argument length greater than length of key variable Indicator_1 at line 47 column 11.
ERROR: Keys and data must be specified in the same order and have the same types as given in DefineKey/DefineData at line 47 column
11.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

As you can see, I set the Indicator field to the length of the Indicator_1 field in the other table using the length statement before the null set statement.

When I remove the quotes around the "Indicator" field in the find statement, I get the following errors:

ERROR: Type mismatch for key variable Indicator at line 48 column 11.
ERROR: Keys and data must be specified in the same order and have the same types as given in DefineKey/DefineData at line 48 column
11.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

The Indicator and Indicator_1 fields are both character fields. Why isn't the merge working?

Re: Hash Merge Using Explicit Key Fields

novinosrin — Tue, 16 Apr 2019 22:12:22 GMT

First off, you are missing END; in the IF THEN DO block

if _n_=1 then do;

		dcl hash one (dataset:'toodle');
			one.definekey ('Product','Status','Indicator_1');
/*			one.definekey ('Product','Status');*/
			one.definedata('core_1');
			one.definedone();
END;

Is 'Indicator' here

rc_1 = one.find(key:'Product'n,key:Status,key:'Indicator');

a variable or constant???

If variable, there shouldn't be quotes.

Re: Hash Merge Using Explicit Key Fields

theponcer — Wed, 17 Apr 2019 13:57:58 GMT

It is a field, but even when I remove the quotes (like I mentioned above), I still get an error that says the types are not the same. Both Indicator fields are Character fields.

Re: Hash Merge Using Explicit Key Fields

novinosrin — Wed, 17 Apr 2019 14:02:25 GMT

Would it be possible to post a sample of your data(a mock closely resembling the exact) and what your objective?

Re: Hash Merge Using Explicit Key Fields

ScottBass — Thu, 18 Apr 2019 08:26:20 GMT

Unlike column definitions in "usual" data step code, hash objects don't create (default) column attributes. You must define the column attributes in the data step prior to defining the hash object.

Often this is done using the construct:

if 0 then set my_lookup_dataset;

which sets the attributes but does not read any data. Otherwise you can use length or attrib statements.

Next, the find() function uses the data values for the key variables that are currently in the PDV to lookup the corresponding keys in the hash object.

I have never used a dynamic key variable, i.e.

one.find(key:'Product'n,key:Status,key:'Indicator');

In this code, either the 2nd key column would be the value of the Status variable (unlikely, if that's even possible? I'm too lazy to lookup the syntax right now), or else it's a typo, and Status should be 'Status'. I suspect it's a typo, unless the value of Status really is the name of a column (again, if that's possible?)

But there's nothing to keep you from "fiddling" with the values in the PDV at run time.

For example:

data test_table ;
   set work.test;
   if 0 then set toodle ;
   length Indicator $5;  
   * if Indicator is not in work.test then what is setting its value? Otherwise it would always be missing ;
   * and if Indicator is in work.test then why the length statement? ;

   if _n_=1 then do;
      dcl hash one (dataset:'toodle');
      one.definekey ('Product','Status','Indicator');
      one.definedata('core_1');
      one.definedone();
   end;

   rc_1 = one.find();

   if (rc_1 ne 0) then do;
      Indicator = Indicator_1;
      rc_1 = one.find();
   end;
   if (rc_1 ne 0) then do;
      Indicator = Indicator_2;
      rc_1 = one.find();
   end;

   /* etc */
run;

Is Indicator_1 in work.test or work.toddle? If in both, do they have the same attributes, i.e. length and type?

I don't know if these macros would help, but you can at least look at the use cases in the header to perhaps get some ideas:

https://github.com/scottbass/SAS/blob/master/Macro/hash_define.sas

https://github.com/scottbass/SAS/blob/master/Macro/hash_lookup.sas

HTH...