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
Hi,
Try removing the quotes from the call missing routine, so that it looks like:
call missing(numerator);
Thanks & kind regards,
Amir.
Hi,
Try removing the quotes from the call missing routine, so that it looks like:
call missing(numerator);
Thanks & kind regards,
Amir.
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.
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;
CALL MISSING needs variables, not character literals, as already mentioned by @Amir.
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;
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.
May you please show how to apply it in the full code?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: