topic Re: Filter a dataset by composite key to leave only matched records with hash object in SAS Programming

Filter a dataset by composite key to leave only matched records with hash object

JanKwiatkowski — Wed, 03 Jan 2024 13:43:04 GMT

Hello!

I want to leave records from Table2 which match composite key from Table1 loaded to hash object but get an error

data merged;
	if 0 then set Table2;
	if _n_ = 1 then do;
		declare hash h(dataset: 'Table1(keep=MOV_CENTRO_ALTA MOV_CUENTA MOV_NUMER_MOV)');
		h.defineKey('MOV_CENTRO_ALTA', 'MOV_CUENTA', 'MOV_NUMER_MOV');
		h.DefineData('MOV_CENTRO_ALTA', 'MOV_CUENTA', 'MOV_NUMER_MOV');
		h.defineDone();
	end;

	set Table2;

	if h.find(key: 'DES_CENTRO_ALTA', key: 'DES_CUENTA', key: 'DES_NUMER_MOV')=0 then output;
run;

ERROR: Undeclared key symbol MOV_CENTRO_ALTA for hash object at line 34 column 3.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

Re: Filter a dataset by composite key to leave only matched records with hash object

Al14 — Wed, 03 Jan 2024 13:52:54 GMT

I think you have a typo. You probably want

if 0 then set Table1;

That statement is putting all of the columns from Table1 into your PDV during compilation

Re: Filter a dataset by composite key to leave only matched records with hash object

Kurt_Bremser — Wed, 03 Jan 2024 14:18:59 GMT

The expression following the KEY: keyword must result in a value, not a variable name.

Therefore

if h.find(key: DES_CENTRO_ALTA, key: DES_CUENTA, key: DES_NUMER_MOV)=0

Re: Filter a dataset by composite key to leave only matched records with hash object

Tom — Wed, 03 Jan 2024 17:18:48 GMT

So I see separate issues in this code.

First you are not defining the variables that only appear in TABLE1.

I suspect you meant to do something like:

if 0 then set Table1(keep=MOV_CENTRO_ALTA MOV_CUENTA MOV_NUMER_MOV);

Second you are only looking in the hash for a single value. The record where

MOV_CENTRO_ALTA='DES_CENTRO_ALTA'
MOV_CUENTA='DES_CUENTA' 
MOV_NUMER_MOV='DES_NUMER_MOV'

If that is what you wanted to do then just use a WHERE clause when searching TABLE2.

I suspect that wanted to use the values of the variables from TABLE2 to search the hash. So remove the quotes around the variable names in the FIND() call.

h.find(key: DES_CENTRO_ALTA ,key: DES_CUENTA ,key: DES_NUMER_MOV)

Personally I find it clearer to treat the return codes as boolean values.

if not h.find(....) then ...

But since it is confusing that a TRUE value indicates a FAILURE of the FIND() I can see why you might want to test if the return code is zero instead.

But if you want to compare the result to a specific value then it will be easier to read (scan) if you code the value first since then I don't have to hunt down the closing ) for the FIND() method call to see if you are testing records where the match worked or where it did not work.

if 0=h.find(....) then ...

Also you seem to be just using this to SUBSET the observations from TABLE2 there is no need to store so much data in the hash. Although I believe it actually does save space to store something in the hash.

Also you probably will want to DROP the variables created from TABLE1. In fact why not just rename them to match. Then there is no need to define them with an extra SET or to drop them . Plus the coding is easier.

data merged;
  if _n_ = 1 then do;
    declare hash h(dataset: 'Table1(keep=MOV_CENTRO_ALTA MOV_CUENTA MOV_NUMER_MOV
    rename=(MOV_CENTRO_ALTA=DES_CENTRO_ALTA MOV_CUENTA=DES_CUENTA MOV_NUMER_MOV=DES_NUMER_MOV
    )');
    h.defineKey('DES_CENTRO_ALTA', 'DES_CUENTA', 'DES_NUMER_MOV');
    h.DefineData('DES_NUMER_MOV');
    h.defineDone();
  end;
  set Table2;
  if h.find() then delete;
run;