BookmarkSubscribeRSS Feed
JanKwiatkowski
Calcite | Level 5

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. 

3 REPLIES 3
Al14
SAS Employee

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

Tom
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 294 views
  • 3 likes
  • 4 in conversation