Cryptic error while using hash objects with cas tables

[acordes]

@Patrick I'm willing to learn hash objects. 

I'm trying to replicate your proposal from a recent post where I complained about slow fedsql performance.

But it fails stating a 'The action stopped due to errors' message. 

It's meant to replace the slow performing fedsql.

proc cas;
source ETL_AC;
		create table PUBLIC.X_TEST1{options replace=true} as 
			select a.*, movave3_batch, movstd3_batch, movave&mov_minutes1., movstd&mov_minutes1., movave&mov_minutes2., movstd&mov_minutes2.
               from CASUSER.TESTER1 a 
					left join &incas..MOVE_BATCH3_TR b 
						on a.batch_id=b.batch_id and a._NAME_=b.name
					left join &incas..MOVE_MIN&mov_minutes1._TR c 
						on a.batch_id=c.batch_id and a._NAME_=c.name and a.datetime=c.datetime  
					left join &incas..MOVE_MIN&mov_minutes2._TR d 
						on a.batch_id=c.batch_id and a._NAME_=d.name and a.datetime=d.datetime 
;
endsource;
fedSQL.execDirect / query=ETL_AC;
quit;

%let mov_minutes1=10;
%let mov_minutes2=30;
%let incas=public;

options mlogic symbolgen merror;

data PUBLIC.X_TEST1(drop=_rc copies=0 replace=yes );
    if _N_ = 1 then do;
        /* create variables */
        if 0 then
          do;
            set 
              CASUSER.TESTER1
              PUBLIC._MOVE_BATCH3_TR(keep=name BATCH_ID movave3_batch movstd3_batch rename=(name=_name_ ))
              PUBLIC._MOVE_MIN10_TR (keep=name movave10 movstd10 BATCH_ID datetime rename=(name=_name_ ))
              PUBLIC._MOVE_MIN30_TR  (keep=name movave30 movstd30 BATCH_ID datetime rename=(name=_name_ ))
              ;
          end;
        /* define hash tables */
        declare hash h_summary_a1(dataset: "&incas.._MOVE_BATCH3_TR(rename=(name=_name_ ))");
        h_summary_a1.defineKey('batch_id', '_name_');
        h_summary_a1.defineData('movave3_batch', 'movstd3_batch');
        h_summary_a1.defineDone();

        declare hash h_summary_a2(dataset: "&incas.._MOVE_MIN&mov_minutes1._TR(rename=(name=_name_ ))");
        h_summary_a2.defineKey('batch_id', '_name_', 'datetime');
        h_summary_a2.defineData("movave&mov_minutes1.", "movstd&mov_minutes1.");
        h_summary_a2.defineDone();

        declare hash h_ndist_a2(dataset: "&&incas.._MOVE_MIN&mov_minutes2._TR(rename=(name=_name_ ))");
        h_ndist_a2.defineKey('batch_id', '_name_', 'datetime');
        h_ndist_a2.defineData("movave&mov_minutes2.", "movstd&mov_minutes2.");
        h_ndist_a2.defineDone();

    end;
    call missing(of _all_);

    set CASUSER.TESTER1;

    /* lookup */
    _rc = h_summary_a1.find();
    _rc = h_summary_a2.find();
    _rc = h_ndist_a2.find();

run;

86   data PUBLIC.X_TEST1(drop=_rc copies=0 replace=yes );
87       if _N_ = 1 then do;
88           /* create variables */
89           if 0 then
90             do;
91               set
92                 CASUSER.TESTER1
93                 PUBLIC._MOVE_BATCH3_TR(keep=name BATCH_ID movave3_batch movstd3_batch rename=(name=_name_ ))
94                 PUBLIC._MOVE_MIN10_TR (keep=name movave10 movstd10 BATCH_ID datetime rename=(name=_name_ ))
95                 PUBLIC._MOVE_MIN30_TR  (keep=name movave30 movstd30 BATCH_ID datetime rename=(name=_name_ ))
96                 ;
97             end;
98           /* define hash tables */
99           declare hash h_summary_a1(dataset: "&incas.._MOVE_BATCH3_TR(rename=(name=_name_ ))");
SYMBOLGEN:  Macro variable INCAS resolves to public
100          h_summary_a1.defineKey('batch_id', '_name_');
101          h_summary_a1.defineData('movave3_batch', 'movstd3_batch');
102          h_summary_a1.defineDone();
103  
104          declare hash h_summary_a2(dataset: "&incas.._MOVE_MIN&mov_minutes1._TR(rename=(name=_name_ ))");
SYMBOLGEN:  Macro variable INCAS resolves to public
SYMBOLGEN:  Macro variable MOV_MINUTES1 resolves to 10
105          h_summary_a2.defineKey('batch_id', '_name_', 'datetime');
106          h_summary_a2.defineData("movave&mov_minutes1.", "movstd&mov_minutes1.");
SYMBOLGEN:  Macro variable MOV_MINUTES1 resolves to 10
SYMBOLGEN:  Macro variable MOV_MINUTES1 resolves to 10
107          h_summary_a2.defineDone();
108  
109          declare hash h_ndist_a2(dataset: "&&incas.._MOVE_MIN&mov_minutes2._TR(rename=(name=_name_ ))");
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable INCAS resolves to public
SYMBOLGEN:  Macro variable MOV_MINUTES2 resolves to 30
110          h_ndist_a2.defineKey('batch_id', '_name_', 'datetime');
111          h_ndist_a2.defineData("movave&mov_minutes2.", "movstd&mov_minutes2.");
SYMBOLGEN:  Macro variable MOV_MINUTES2 resolves to 30
SYMBOLGEN:  Macro variable MOV_MINUTES2 resolves to 30
112          h_ndist_a2.defineDone();
113  
114      end;
115      call missing(of _all_);
116  
117      set CASUSER.TESTER1;
118  
119      /* lookup */
120      _rc = h_summary_a1.find();
121      _rc = h_summary_a2.find();
122      _rc = h_ndist_a2.find();
123  
124  run;
NOTE: Running DATA step in Cloud Analytic Services.
NOTE: The DATA step will run in multiple threads.
ERROR: The action stopped due to errors.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.36 seconds
      cpu time            0.06 seconds
      
SYMBOLGEN:  Macro variable GRAPHTERM resolves to goptions noaccessible;
125  
126  /* region: Generated postamble */
127  /* Close ODS destinations */
128  &graphterm; ;*';*";*/;run;quit;
129  quit;run;
130  ods html5 (id=web) close;
131  ods listing close;
132  %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
133      filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
134  %end;
135  %studio_capture_custom_output;
MLOGIC(STUDIO_CAPTURE_CUSTOM_OUTPUT):  Beginning execution.
SYMBOLGEN:  Macro variable _DATAOUT_NAME resolves to 
SYMBOLGEN:  Macro variable _DATAOUT_MIME_TYPE resolves to 
MLOGIC(STUDIO_CAPTURE_CUSTOM_OUTPUT):  %IF condition "&_dataout_name" ne "" and "&_dataout_mime_type" ne "" is FALSE
MLOGIC(STUDIO_CAPTURE_CUSTOM_OUTPUT):  Ending execution.
136  /* endregion */
137  

[SASJedi]

You can get more information on what is causing your issue by running the DATA step in CAS using the CAS runCode action and requesting status information. Here is an example:

proc cas;
source myDATAstep;
/* Put all of your DATA step code in here */
endsource;

datastep.runCode status=status/
   code=myDATAstep
;
print status;
run;
quit;

This should produce more info in the log, something like this:

ERROR: The action stopped due to errors.
{severity=2,reason=6,status=Action stopped due to client interrupt request.,statusCode=2620049}

You can refer to SAS Help Center: Severity Codes and SAS Help Center: Status and Reason Codes for help understanding the severity and reason codes. 

[acordes]

@SASJedi thanks.

{severity=2,reason=6,status=Action stopped due to client interrupt request.,statusCode=2620049}

[SASJedi]

I wrote a DATA step with a hash object lookup that ran without error in CAS. Then I created a simplified version of your code and was able to reproduce the error shown here. I've submitted a SAS tech support request, as I can't find any information about the statusCode value and the other information didn't inspire an answer. I'll post back here when we ge it sorted out.

[Patrick]

Thanks @SASJedi and very curious what SAS TS comes back with.

[SASJedi]

This turns out to be a problem with the SAS Online Docs. The use of data set options for loading a hash object is not supported in CAS. I've reached out to the documentation team and expect to see a note added to that effect in the next documentation update when it is published next month.

[Patrick]

---

@SASJedi wrote:

This turns out to be a problem with the SAS Online Docs. The use of data set options for loading a hash object is not supported in CAS. I've reached out to the documentation team and expect to see a note added to that effect in the next documentation update when it is published next month.

---

@acordes Based on above removing the data set options from your code should resolve the issue.

%let mov_minutes1=10;
%let mov_minutes2=30;
%let incas=public;

options mlogic symbolgen merror;

data PUBLIC.X_TEST1(drop=_rc copies=0 replace=yes );
    if _N_ = 1 then do;
        /* create variables */
        if 0 then
          do;
            set 
              CASUSER.TESTER1
              PUBLIC._MOVE_BATCH3_TR(keep=name BATCH_ID movave3_batch movstd3_batch)
              PUBLIC._MOVE_MIN10_TR (keep=name movave10 movstd10 BATCH_ID datetime)
              PUBLIC._MOVE_MIN30_TR  (keep=name movave30 movstd30 BATCH_ID datetime)
              ;
          end;
        /* define hash tables */
        declare hash h_summary_a1(dataset: "&incas.._MOVE_BATCH3_TR");
        h_summary_a1.defineKey('batch_id', 'name');
        h_summary_a1.defineData('movave3_batch', 'movstd3_batch');
        h_summary_a1.defineDone();

        declare hash h_summary_a2(dataset: "&incas.._MOVE_MIN&mov_minutes1._TR");
        h_summary_a2.defineKey('batch_id', 'name', 'datetime');
        h_summary_a2.defineData("movave&mov_minutes1.", "movstd&mov_minutes1.");
        h_summary_a2.defineDone();

        declare hash h_ndist_a2(dataset: "&&incas.._MOVE_MIN&mov_minutes2._TR");
        h_ndist_a2.defineKey('batch_id', 'name', 'datetime');
        h_ndist_a2.defineData("movave&mov_minutes2.", "movstd&mov_minutes2.");
        h_ndist_a2.defineDone();

    end;
    call missing(of _all_);

    set CASUSER.TESTER1;

    /* lookup */
    _name_=name;
    _rc = h_summary_a1.find();
    _rc = h_summary_a2.find();
    _rc = h_ndist_a2.find();
    drop _name_;

run;