We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Error Identification/error correction/ number of obs code in Proc SQL

by Regular Learner Anind_Sharma on ‎06-10-2014 02:57 AM - edited on ‎10-05-2015 03:48 PM by Community Manager (284 Views)

/For identification of the error in the proc sql and the Number of observation procesed by the step/

/*add below 4 lines in the end of each Proc SQL Step. */

 

 

%let Error_cd1 = &SQLRC;

%let Numobs1 =&SQLObs;

quit;

%put &NumObs1 Error_CD1;

.

.

.to

.

%let Error_cd23 = &SQLRC;

%let Numobs23 =&SQLObs;

quit;

%put &NumObs23 Error_CD23;

 

 

%macro cre;

%do j=1 %to 24;

     proc sql;

          insert into error_log values(&&error_cd&j, &&NumObs&j);

     quit;

%end;

%mend;

%cre

 

/After running the above macro Cre the output error_log will contains all error code along with the number of observation processed by the step/

 

Error Code can be identified by the below error code definition:

 

RC Definition

0  PROC SQL statement completed successfully with no errors.

4  PROC SQL statement encountered a situation for which it issued a warning. The statement continued to execute.

8  PROC SQL statement encountered an error. The statement stopped execution at this point.

12 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during compile time.

16 PROC SQL statement encountered a user error. For example, this error code is used, when a subquery (that can return only a single value) evaluates to more than one row. These errors can be detected only during run time.

   Leveraging SQL Return Codes When Querying Relational Databases

24 PROC SQL statement encountered a system error. For example, this error is used, if the system cannot write to a PROC SQL table because the disk is full. These errors can occur only during run time.

28 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during run time.

Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.