sqlxrc error code issue

[AshokD]

Hi,

I am using PROC SQL to perform some insert records using SAS - TERADATA.

I didn't use library reference(to connect with database) prior to the PROC SQL step so the code was error out which is expected.

ERROR: Libname TERADATA is not assigned.

After PROC SQL step, I am printing &SQLXRC to check error code so based on that I am printing messages.

%put SQL Error Code : &sqlxrc.;

%if &sqlrc. eq 0 %then %do;

data _null_;
put "Job has been Completed";
run;
%end;

%else %do;
data _null_;
put "Job end with error";
%end;

The issue is, code was ended with error but still &SQLXRC resolves to 0 and the message shows job has been completed. I am not sure, What I'm missing here ? Can someone help me to resolve this issue.

[ChrisNZ · Accepted solution]

The doc states: SQLXRC contains DBMS specific error codes.

The error here is a SAS error. No database code was executed, so there is no DBMS error and the macro variable kept its default value of 0. 

You can also use variables SQLRC for proc sql errors, SYSRC for OS errors, and SYSERR for SAS errors.

[AshokD]

It work's now, thanks Chris

[Tom]

Which macro variable are you trying to access SQLXRC or SQLRC?

If you want the value of SQLXRC make sure to save it into your own macro variable before running any other SQL commands that access the a remote database since it is reset after each statement.