topic Re: Merge data sets using Hash method in SAS Programming

Merge data sets using Hash method

Ronein — Thu, 07 Sep 2023 06:50:45 GMT

Hello

I want to ask about Merge using Hash method.

I receive an error.


Data tbl1;
Format date date9.;
Input Name$ date :date9. Amnt1;
cards;
A 01JAN2023 100
A 02JAN2023 150
A 03JAN2023 200
A 04JAN2023 250
B 02JAN2023 200
B 04JAN2023 300
B 06JAN2023 400
C 03JAN2023 300
C 08JAN2023 400
C 15JAN2023 500
;
run;

Data tbl2;
Input Name$ numerator W;
cards;
A 111 1000
B 222 2000
C 333 3000
D 444 4000
;
run;

/***Way1***/
proc sort data = tbl1;
by Name;
run;
proc sort data = tbl2;
by Name;
run;
data Way1;      
merge tbl1 (in=a) tbl2(in=b);
by Name;
IF a and b;
run;


/***Way2***/
data Way2;
set tbl1;
if _n_ = 1
then do;
declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
tbl2.definekey("Name");
tbl2.definedata("numerator");
tbl2.definedone();
call missing("numerator");
end;
rc = tbl2.find();
drop rc;
run;

Here is the error

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

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
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         /***Way2***/
27         data Way2;
28         set tbl1;
29         if _n_ = 1
30         then do;
31         declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
32         tbl2.definekey("Name");
33         tbl2.definedata("numerator");
34         tbl2.definedone();
35         call missing("numerator");
                        ___________
                        135
ERROR 135-185: Attempt to change the value of the constant "numerator" in the MISSING subroutine call.

36         end;
37         rc = tbl2.find();
38         drop rc;
39         run;

NOTE: Compression was disabled for data set WORK.WAY2 because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WAY2 may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
WARNING: Data set WORK.WAY2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              546.93k
      OS Memory           25768.00k
      Timestamp           09/07/2023 09:40:49 AM
      Step Count                        131  Switch Count  0
      Page Faults                       0
2                                                          The SAS System                          07:19 Thursday, September 7, 2023

      Page Reclaims                     34
      Page Swaps                        0
      Voluntary Context Switches        15
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

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

Here is the error

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

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
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         /***Way2***/
27         data Way2;
28         set tbl1;
29         if _n_ = 1
30         then do;
31         declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
32         tbl2.definekey("Name");
33         tbl2.definedata("numerator");
34         tbl2.definedone();
35         call missing("numerator");
                        ___________
                        135
ERROR 135-185: Attempt to change the value of the constant "numerator" in the MISSING subroutine call.

36         end;
37         rc = tbl2.find();
38         drop rc;
39         run;

NOTE: Compression was disabled for data set WORK.WAY2 because compression overhead would increase the size of the data set.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WAY2 may be incomplete.  When this step was stopped there were 0 observations and 3 variables.
WARNING: Data set WORK.WAY2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              546.93k
      OS Memory           25768.00k
      Timestamp           09/07/2023 09:40:49 AM
      Step Count                        131  Switch Count  0
      Page Faults                       0
2                                                          The SAS System                          07:19 Thursday, September 7, 2023

      Page Reclaims                     34
      Page Swaps                        0
      Voluntary Context Switches        15
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

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

Re: Merge data sets using Hash method

Amir — Thu, 07 Sep 2023 06:55:38 GMT

Hi,

Try removing the quotes from the call missing routine, so that it looks like:

call missing(numerator);

Thanks & kind regards,

Amir.

Re: Merge data sets using Hash method

Kurt_Bremser — Thu, 07 Sep 2023 07:21:53 GMT

You do not properly define numerator; either use a LENGTH statement, or

if 0 then set tbl2 (keep=name numerator);

at the beginning of the DATA step to get the variable into the PDV.

The second method will automatically take care of attribute changes.

Re: Merge data sets using Hash method

Ronein — Thu, 07 Sep 2023 07:51:42 GMT

Thanks,

May you please show the full code?

I tried this one

data Way2;
set tbl1;
if _n_ = 1
then do;
declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
if 0 then set tbl2 (keep=name numerator);
tbl2.definekey("Name");
tbl2.definedata("numerator");
tbl2.definedone();
call missing("numerator");
end;
rc = tbl2.find();
drop rc;
run;

Re: Merge data sets using Hash method

Kurt_Bremser — Thu, 07 Sep 2023 07:53:25 GMT

CALL MISSING needs variables, not character literals, as already mentioned by @Amir.

Re: Merge data sets using Hash method

Ronein — Thu, 07 Sep 2023 08:09:57 GMT

Thanks,Now it is working 🙂

May you please show where should write the sentence if 0 then ...?

Is the sentence (if 0 then ) is alternative to call missing sentence?

set tbl1;
if _n_ = 1
then do;
declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
tbl2.definekey("Name");
tbl2.definedata("numerator");
tbl2.definedone();
call missing(numerator);
end;
rc = tbl2.find();
drop rc;
run;

Re: Merge data sets using Hash method

Kurt_Bremser — Thu, 07 Sep 2023 08:14:47 GMT

IF 0 means that the following statement will never be executed at runtime, but during compilation the data step compiler will read the dataset metadata and include the variables in the PDV.

And I would insert the statement immediately after the unconditional SET, for clarity.

Re: Merge data sets using Hash method

Ronein — Thu, 07 Sep 2023 08:15:19 GMT

May you please show how to apply it in the full code?

Re: Merge data sets using Hash method

Kurt_Bremser — Thu, 07 Sep 2023 10:43:38 GMT

data way2;
set tbl1;
if 0 then set tbl2 (keep=name numerator);
if _n_ = 1
then do;
  declare hash tbl2 (dataset:"tbl2 (keep=Name numerator)");
  tbl2.definekey("Name");
  tbl2.definedata("numerator");
  tbl2.definedone();
  call missing(numerator);
end;
rc = tbl2.find();
drop rc;
run;