BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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         

 

1 ACCEPTED SOLUTION

Accepted Solutions
Amir
Ammonite | Level 13

Hi,

 

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

 

call missing(numerator);

 

 

Thanks & kind regards,

Amir.

View solution in original post

8 REPLIES 8
Amir
Ammonite | Level 13

Hi,

 

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

 

call missing(numerator);

 

 

Thanks & kind regards,

Amir.

Kurt_Bremser
Super User

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.

Ronein
Meteorite | Level 14

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;
Ronein
Meteorite | Level 14

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;
Kurt_Bremser
Super User

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.

Ronein
Meteorite | Level 14

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

 

Kurt_Bremser
Super User
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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 8 replies
  • 248 views
  • 5 likes
  • 3 in conversation