topic Re: Merge data sets using Hash method- error due to rename problem in SAS Programming

Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 10:21:51 GMT

Hello

I am using Hash method to merge data sets.

The by variables to merge have different names.

Here is the code I run that produce error.

In real life data sets are very big so I don't want to rename variables of data set VBM374_USED_BRANCH_CUSTOMER.

It means that the rename should be done during Hash process and not before.

Data ABT_ALL;
input lak_id snif X y Z;
cards;
111111 987 10 20 30
222222 921 15 30 45
333333 940 20 40 60
;
Run;


Data VBM374_USED_BRANCH_CUSTOMER;
input Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP;
cards;
222222 921 123 
333333 940 456
;
run;


data ABT_ALL_b;
set ABT_ALL;
if _n_ = 1
then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
h.definekey("Branch_Cust_Nbr","Branch_Nbr"); 
h.definedata("Numerator_DWH");
h.definedone();
if 0 then set VBM374_USED_BRANCH_CUSTOMER (keep=Branch_Cust_Nbr  Branch_Nbr first_Branch_Cust_IP rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif first_Branch_Cust_IP=Numerator_DWH));
call missing(Numerator_DWH);
end;
rc = h.find();
drop rc;
run;

The error

1                                                          The SAS System                          07:19 Thursday, September 7, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (6)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         data ABT_ALL_b;
27         set ABT_ALL;
28         if _n_ = 1
29         then do;
30         declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
31         h.definekey("Branch_Cust_Nbr","Branch_Nbr");
32         h.definedata("Numerator_DWH");
33         h.definedone();
34         if 0 then set VBM374_USED_BRANCH_CUSTOMER (keep=Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP
34       ! rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif first_Branch_Cust_IP=Numerator_DWH));
35         call missing(Numerator_DWH);
36         end;
37         rc = h.find();
38         drop rc;
39         run;

ERROR: Undeclared key symbol Branch_Cust_Nbr for hash object at line 33 column 1.
ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.ABT_ALL.
WARNING: The data set WORK.ABT_ALL_B may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              810.68k
      OS Memory           26024.00k
      Timestamp           09/07/2023 01:18:27 PM
      Step Count                        168  Switch Count  2
      Page Faults                       0
      Page Reclaims                     172
      Page Swaps                        0
2                                                          The SAS System                          07:19 Thursday, September 7, 2023

      Voluntary Context Switches        44
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0
      

40         
41         GOPTIONS NOACCESSIBLE;
42         %LET _CLIENTTASKLABEL=;
43         %LET _CLIENTPROCESSFLOWNAME=;
44         %LET _CLIENTPROJECTPATH=;
45         %LET _CLIENTPROJECTPATHHOST=;
46         %LET _CLIENTPROJECTNAME=;
47         %LET _SASPROGRAMFILE=;
48         %LET _SASPROGRAMFILEHOST=;
49         
50         ;*';*";*/;quit;run;
51         ODS _ALL_ CLOSE;
52         
53         
54         QUIT; RUN;
55

Re: Merge data sets using Hash method- error due to rename problem

andreas_lds — Thu, 07 Sep 2023 10:24:48 GMT

Move if 0 then set .... before declaring the hash.

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 11:03:42 GMT

No work,still error

ERROR: Undeclared key symbol Branch_Cust_Nbr for hash object at line 33 column 1.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

data ABT_ALL_b;
set ABT_ALL;
if _n_ = 1 then do;
if 0 then set VBM374_USED_BRANCH_CUSTOMER (keep=Branch_Cust_Nbr  Branch_Nbr first_Branch_Cust_IP rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif first_Branch_Cust_IP=Numerator_DWH));
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
h.definekey("Branch_Cust_Nbr","Branch_Nbr"); 
h.definedata("Numerator_DWH");
h.definedone();
call missing(Numerator_DWH);
end;
rc = h.find();
drop rc;
run;

Re: Merge data sets using Hash method- error due to rename problem

Amir — Thu, 07 Sep 2023 12:03:25 GMT

Hi,

It looks like you are renaming Branch_Cust_Nbr to lak_id, but later you refer back to Branch_Cust_Nbr. Try instead to refer to lak_id, and the same goes for any other variables you have renamed and then are referring to later.

You might also need to have a rename of the same variables when declaring the hash, if possible, otherwise maybe remove the rename.

Thanks & kind regards,

Amir.

Edit: Typo.

Edit2: Added second suggestion.

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 12:54:51 GMT

May you please show code Amir?

Re: Merge data sets using Hash method- error due to rename problem

andreas_lds — Thu, 07 Sep 2023 13:49:08 GMT

You are renaming Branch_Cust_Nbr to lak_id, so you can't use Branch_Cust_Nbr as key in the hash object

set ... rename=(Branch_Cust_Nbr=lak_id ...

Re: Merge data sets using Hash method- error due to rename problem

Kurt_Bremser — Thu, 07 Sep 2023 15:15:16 GMT

Your IF 0 THEN SET has a RENAME= dataset option, which means the variables are defined into the PDV with other names than those you use in the DEFINE methods.

Use the same options in the DECLARE statement, and use the RENAMEd names in the DEFINEs.

Re: Merge data sets using Hash method- error due to rename problem

Amir — Thu, 07 Sep 2023 15:59:10 GMT

Hi @Ronein,

I hope it is clear what is required now, based on the follow up from @andreas_lds and @Kurt_Bremser.

When renaming variables for an incoming data set, e.g., old_name = new_name, you should refer to new_name in the rest of the data step.

If after making changes you're still getting issues then please post the log showing the data step that you've tried to fix and any notes, warnings or error messages, using the Insert Code icon "</>".

Thanks & kind regards,

Amir.

Re: Merge data sets using Hash method- error due to rename problem

Tom — Thu, 07 Sep 2023 16:37:34 GMT

Why all of the renames? Just use the KEY: option on the FIND() command to tell the hash object which variables have the key.

data ABT_ALL;
  input lak_id snif X y Z;
cards;
111111 987 10 20 30
222222 921 15 30 45
333333 940 20 40 60
;

data VBM374_USED_BRANCH_CUSTOMER;
  input Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP;
cards;
222222 921 123 
333333 940 456
;

data ABT_ALL_b;
  set ABT_ALL;
  if 0 then set VBM374_USED_BRANCH_CUSTOMER(keep=Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP);
  if _n_ = 1 then do;
    declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
    h.definekey("Branch_Cust_Nbr","Branch_Nbr"); 
    h.definedata("first_Branch_Cust_IP");
    h.definedone();
  end;
  if not h.find(key:lak_id,key:snif) then Numerator_DWH=first_Branch_Cust_IP;
  drop Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP ;
run;

proc print;
run;

Result

Obs    lak_id    snif     X     y     Z    Numerator_DWH

 1     111111     987    10    20    30         .
 2     222222     921    15    30    45       123
 3     333333     940    20    40    60       456

If you do want to rename them then do in the DATASET: option and the DEFINEKEY() and DEFINEDATA() options.

data ABT_ALL_b;
  set ABT_ALL;
  if _n_ = 1 then do;
    declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER
     (rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif
             first_Branch_Cust_IP=Numerator_DWH))"
    );
    h.definekey("lak_id","snif"); 
    h.definedata("Numerator_DWH");
    h.definedone();
  end;
  if h.find() then Numerator_DWH=.;
run;

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 20:39:45 GMT

But the rename was done on data set "VBM374_USED_BRANCH_CUSTOMER" and not on data set "ABT_ALL" . Here you wrote the rename on data set ABT_ALL

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 20:42:04 GMT

Do you mean that in h.definekey and h.definedata I should use the renamed columns?

This code still have error

ERROR: Variable lak_id is not on file WORK.VBM374_USED_BRANCH_CUSTOMER.
ERROR: Hash data set load failed at line 33 column 1.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

data WANT;
set ABT_ALL;
if _n_ = 1
then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
h.definekey("lak_id","snif"); 
h.definedata("Numerator_DWH");
h.definedone();
if 0 then set VBM374_USED_BRANCH_CUSTOMER (keep=Branch_Cust_Nbr  Branch_Nbr first_Branch_Cust_IP
                                           rename=(Branch_Cust_Nbr=lak_id   Branch_Nbr=snif   first_Branch_Cust_IP=Numerator_DWH));
call missing(Numerator_DWH);
end;
rc = h.find();
drop rc;
run;

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Thu, 07 Sep 2023 20:46:58 GMT

Great great,

So SAS knows to connect Branch_Cust_Nbr with lak_id and Branch_Nbr with snif?

I also want to ask please- Is Hash method Sort the data (in the background)?

What is the advantage if using this method for merging data sets?

Re: Merge data sets using Hash method- error due to rename problem

Kurt_Bremser — Thu, 07 Sep 2023 20:50:32 GMT

You really need to read our posts. Quote from mine:

Use the same options in the DECLARE statement

You simply did not do that.

Details are important in computing.

Re: Merge data sets using Hash method- error due to rename problem

Tom — Fri, 08 Sep 2023 04:32:00 GMT

@Ronein wrote:

Great great,

So SAS knows to connect Branch_Cust_Nbr with lak_id and Branch_Nbr with snif?

I also want to ask please- Is Hash method Sort the data (in the background)?

What is the advantage if using this method for merging data sets?

It does not so much sort the data has put into bins to make it easier to find.

If the datasets are already sorted (and why aren't they) then the fastest method is a normal data step MERGE.

If one of the datasets is small enough to fit into memory than loading it into a HASH object means you do not have to sort the other (larger) dataset. That can be doubly important if you need that dataset sorted in some other way for you later operations.

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Fri, 08 Sep 2023 04:34:44 GMT

Thanks friends,

Here is the summary of the solutions:

Data ABT_ALL;
input lak_id snif X y Z;
cards;
111111 987 10 20 30
222222 921 15 30 45
333333 940 20 40 60
;
Run;

Data VBM374_USED_BRANCH_CUSTOMER;
input Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP;
cards;
222222 921 123 
333333 940 456
;
run;

/************Left join*****************************************/
/************Left join*****************************************/
/************Left join*****************************************/
data WAY1;
set ABT_ALL;
if _n_ = 1 then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER
                       (rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif
                        first_Branch_Cust_IP=Numerator_DWH))");
h.definekey("lak_id","snif"); 
h.definedata("Numerator_DWH");
h.definedone();
end;
if h.find() then Numerator_DWH=.;
run;


data WAY2;
set ABT_ALL;
if _n_ = 1
then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER
                       (rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif
                        first_Branch_Cust_IP=Numerator_DWH))");
h.definekey("lak_id","snif"); 
h.definedata("Numerator_DWH");
h.definedone();
if 0 then set VBM374_USED_BRANCH_CUSTOMER(rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif first_Branch_Cust_IP=Numerator_DWH));
call missing(Numerator_DWH);
end;
rc = h.find();
drop rc;
run;



data WAY3;
set ABT_ALL;
if 0 then set VBM374_USED_BRANCH_CUSTOMER(keep=Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP);
if _n_ = 1 then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
h.definekey("Branch_Cust_Nbr","Branch_Nbr"); 
h.definedata("first_Branch_Cust_IP");
h.definedone();
end;
if not h.find(key:lak_id,key:snif) then Numerator_DWH=first_Branch_Cust_IP;
drop Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP ;
run;

Re: Merge data sets using Hash method- error due to rename problem

Ronein — Fri, 08 Sep 2023 04:37:49 GMT

I would like to ask please -

In each of the 3 ways of solution via Hash method - Which code is telling SAS to perform LEFT JOIN?

IF I want to perform INNER JOIN-Which code need to be modified ?

IF I want to perform FULL JOIN-which code need to be modified?

Data ABT_ALL;
input lak_id snif X y Z;
cards;
111111 987 10 20 30
222222 921 15 30 45
333333 940 20 40 60
;
Run;

Data VBM374_USED_BRANCH_CUSTOMER;
input Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP;
cards;
222222 921 123 
333333 940 456
;
run;

/************Left join*****************************************/
/************Left join*****************************************/
/************Left join*****************************************/
data WAY1;
set ABT_ALL;
if _n_ = 1 then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER
                       (rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif
                        first_Branch_Cust_IP=Numerator_DWH))");
h.definekey("lak_id","snif"); 
h.definedata("Numerator_DWH");
h.definedone();
end;
if h.find() then Numerator_DWH=.;
run;


data WAY2;
set ABT_ALL;
if _n_ = 1
then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER
                       (rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif
                        first_Branch_Cust_IP=Numerator_DWH))");
h.definekey("lak_id","snif"); 
h.definedata("Numerator_DWH");
h.definedone();
if 0 then set VBM374_USED_BRANCH_CUSTOMER(rename=(Branch_Cust_Nbr=lak_id Branch_Nbr=snif first_Branch_Cust_IP=Numerator_DWH));
call missing(Numerator_DWH);
end;
rc = h.find();
drop rc;
run;



data WAY3;
set ABT_ALL;
if 0 then set VBM374_USED_BRANCH_CUSTOMER(keep=Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP);
if _n_ = 1 then do;
declare hash h (dataset:"VBM374_USED_BRANCH_CUSTOMER");
h.definekey("Branch_Cust_Nbr","Branch_Nbr"); 
h.definedata("first_Branch_Cust_IP");
h.definedone();
end;
if not h.find(key:lak_id,key:snif) then Numerator_DWH=first_Branch_Cust_IP;
drop Branch_Cust_Nbr Branch_Nbr first_Branch_Cust_IP ;
run;

Re: Merge data sets using Hash method- error due to rename problem

Tom — Fri, 08 Sep 2023 04:38:58 GMT

That middle one does not need the IF 0 THEN SET statement.

You are reading in only 3 variables. 2 of them are the KEY variables so those are already defined by the SET for the non-hashed dataset. And the third is a numeric variable, so your use of it in the CALL MISSING() statement will define it already.

Re: Merge data sets using Hash method- error due to rename problem

Tom — Fri, 08 Sep 2023 04:47:45 GMT

Your original problem was doing a LOOKUP. Not really any type of "JOIN". If it wasn't for the fact that you have two key variables instead of one then you could have done that by making a FORMAT or an INFORMAT instead.

None of the data steps you posted are doing what SQL calls a JOIN. They do not handle the situation where both datasets could have multiple observations for the same set of key values. But as long as the small dataset (the one you put into the hash) has unique observations then it is doing LEFT (or RIGHT what is the difference really) JOIN since you keep all of the observations from the main dataset and just add on the values extracted from the dataset that is stored in the hash.

To handle multiple observations for the same key values you would have to define the hash to allow that. And then make multple calls to the .FIND() method until you no longer retrieve another observation. So you would need to add a loop and output statement(s).