SAS 9.4
SAS DI Studio 4.901
I expected the DSNFERR option to return an RC of 8 if not finding a dataset to process.
I also expect it would not continue processing rows (except in SYNTAX CHECK mode).
Can someone suggest something that might be allowing the job to continue as normal after the ERROR: is noted in the log?
I might be missing an option, but we have checked the Batch Server as well as our workspace server to ensure the option is set, but still getting what I consider to be strange behaviour.
<log snippet follows>
Cheers
Bob
Again FYI, this job has RC=0 and so we are missing capture of this ERROR.
<snip>
options DSNFERR;
166
167
168 /*Update Offence Hierarchy*/
169
170 proc sql;
170 ! update PROSEC.STI_BRIEFS_DATA as T1
171 set CATEGORY =
172 (select CATEGORY from PROSEC.ASOC_UNIQUE_TEEEEEEST as T2
173 where t1.STATUTE = t2.STATUTE)
174 ;
ERROR: File PROSEC.ASOC_UNIQUE_TEEEEEEST.DATA does not exist.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2017-05-11T12:20:46,318+08:00| _DISARM| BatchServer| _DISARM| SAS| _DISARM| | _DISARM| 2686782| _DISARM| 12935168| _DISARM| 9| _DISARM| 9| _DISARM| 786| _DISARM| 6661608| _DISARM| 0.000000| _DISARM| 0.000000| _DISARM| 1810095646.319000| _DISARM| 1810095646.319000| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
175 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-05-11T12:20:46,318+08:00| _DISARM| BatchServer| _DISARM| SAS| _DISARM| | _DISARM| 16859136| _DISARM| 12935168| _DISARM| 9| _DISARM| 9| _DISARM| 660742| _DISARM| 6663420| _DISARM| 0.000000| _DISARM| 0.000000| _DISARM| 1810095646.319000| _DISARM| 1810095646.319000| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
176
177 proc sql;
177 ! update PROSEC.STI_BRIEFS_DATA as T1
178 set OFFENCE =
179 (select OFFENCE from PROSEC.ASOC_UNIQUE as T2
180 where t1.STATUTE = t2.STATUTE)
181 ;
NOTE: 2686782 rows were updated in PROSEC.STI_BRIEFS_DATA.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2017-05-11T12:21:24,163+08:00| _DISARM| BatchServer| _DISARM| SAS| _DISARM| | _DISARM| 2612| _DISARM| 15560704| _DISARM| 9| _DISARM| 9| _DISARM| 52239308320| _DISARM| 52246095595| _DISARM| 25.890625| _DISARM| 37.844000| _DISARM| 1810095646.319000| _DISARM| 1810095684.163000| _DISARM| 14.562500| _DISARM| | _ENDDISARM
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2017-05-11T12:21:24,256+08:00| _DISARM| BatchServer| _DISARM| SAS| _DISARM| | _DISARM| 2686782| _DISARM| 15032320| _DISARM| 9| _DISARM| 9| _DISARM| 52238785572| _DISARM| 52246228989| _DISARM| 25.890625| _DISARM| 37.938000| _DISARM| 1810095646.319000| _DISARM| 1810095684.257000| _DISARM| 14.562500| _DISARM| | _ENDDISARM
182 quit;
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-05-11T12:21:24,288+08:00| _DISARM| BatchServer| _DISARM| SAS| _DISARM| | _DISARM| 19632128| _DISARM| 14245888| _DISARM| 9| _DISARM| 9| _DISARM| 52239632174| _DISARM| 52246295833| _DISARM| 25.921875| _DISARM| 37.969000| _DISARM| 1810095646.319000| _DISARM| 1810095684.288000| _DISARM| 14.562500| _DISARM| | _ENDDISARM
NOTE: PROCEDURE SQL used (Total process time):
real time 37.96 seconds
cpu time 25.92 seconds
<snip/>
It's imho not intuitive that SAS doesn't remain in syntax check mode once it encounters the missing file - but that's how it is.
Using option ERRORABEND should resolve the issue for you.
Here some working code for testing.
/*options errorabend;*/
options noerrorabend;
options DSNFERR;
libname prosec (work);
data prosec.STI_BRIEFS_DATA;
category=1;
STATUTE=1;
OFFENCE=.;
run;
proc sql;
update PROSEC.STI_BRIEFS_DATA as T1
set CATEGORY =
(select CATEGORY from PROSEC.ASOC_UNIQUE_TEEEEEEST as T2
where t1.STATUTE = t2.STATUTE)
;
quit;
data PROSEC.ASOC_UNIQUE;
set prosec.STI_BRIEFS_DATA;
OFFENCE=5;
run;
proc sql;
update PROSEC.STI_BRIEFS_DATA as T1
set OFFENCE =
(select OFFENCE from PROSEC.ASOC_UNIQUE as T2
where t1.STATUTE = t2.STATUTE)
;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.