Scheduled job is failing probably because of the Warning message WARNING: Character expression will

[couthelle]

Hello,

I have a scheduled job that keeps on failing.

The SAS runs fine except for a it gets WARNING: Character expression will be truncated when assigned to character column Type.

I was told by our support to configure the scheduler to look for exit code 1 since the SAS tables are being updated as expected. However, if I change this to exit code 1, it won’t be able to detect if the code has an error or if it failed.

Asking the scheduler to look for Exit Code = 0, fails because I have a Warning message.

My questions are:

1. How do I get rid of WARNING: Character expression will be truncated when assigned to character column Type?

• The Type column in the server.final has Type=Char, Len=15, Format = $15., Informat = $15.
• I tried to do something like substr(Type,1,15) length=15 and I also tried altering the character type before inserting to align the format/informat but it didn’t help – I got the idea from here

1. How do I know what is the exit code of my sas program?

• I added %put &=syscc in the end of the program I got code 1012 but I don’t know if that’s the exit code

Here is my sas code, it’s very simple but I noticed that the job is failing when add the insert code.

Really appreciate your help here.

proc import out=work.import
            datafile='directory/a.xls'
            dbms=xls replace ;
            getnames=yes;
run;

proc sql;
insert into server.final (Date, Name, Type, Full_Comments)
select
     a.Date,
     a.Name,
     a.Type,
     a.Full_Comments
from work.import a;
quit;
%put &=syscc;

[sbxkoenk · Accepted solution]

With regard to your 2nd question (exit code) ... I would use &syserr.

Macro Language Reference
SYSCC Automatic Macro Variable
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p11nt7mv7k9hl4n1x9zwkralgq1b.htm

Macro Language Reference
SYSERR Automatic Macro Variable
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n1wrevo4roqsnxn1fbd9yezxvv9k.htm

data want;
 set notfound;
run;

proc options option=errorcheck; run;

%put &=Syscc;
%put &=SysErr; 
%put &=SysWarningText; 
%put &=SysErrorText;
/* end of program */

Koen

[couthelle]

Thanks a bunch @sbxkoenk this is exactly what I'm looking for!

%put &=SysErr; 

[SASKiwi]

I consider it best practice to fix the cause of the warning rather than fixing the symptoms. Try this:

proc sql;
insert into server.final (Date, Name, Type, Full_Comments)
select
     a.Date,
     a.Name,
     a.Type as Type length = 15,
     a.Full_Comments
from work.import a;
quit;

[couthelle]

@SASKiwi thank you very much, really appreciate the advice!

[Patrick]

I'm very much for an approach where SAS code shouldn't return warnings. I believe a warning creates an exit code 1 but an error exit code 2. If you would allow for exit code 1 you still would capture error conditions. 

Excel is just a bad data source for any sort of ETL style process. You can't rely on getting stable results/table structures using Proc Import. The result will always depend on the specific version of the Excel and what data it contains. 

My first question would be: Why do you get a variable with a length longer than expected by your target table? Proc Import creates the length of a character variable using the longest string it finds in the data so likely the Warning is an indication for actual string truncation.

Also important: With the xls engine you need to define guessingrows=max as else string truncation can already occur during import with Proc Import.

[couthelle]

@Patrick I tried to configure the scheduler to look for exit code = 1 after the code ran after deliberately screwing up my code i.e missing ";" but the scheduler didn't detect that error and still ended with Success status. 

As for why I'm using excel, this is actually someone else's task, I'm just trying to automate it without really changing the work file of that person because he didn't know how to code in SAS. Adding guessingrows=max is really helpful, I should have thought of that right away! But really appreciate that feedback!