topic Re: SAS warning message in SAS Programming

SAS warning message

ettikala — Thu, 30 Oct 2014 20:18:31 GMT

Please help to resolve the warning message.

We recieve this warning only after SAS 9.4 Upgrade, earlier SAS (R) 9.1 (TS1M3) never reported this kind of warning. Please advice.

WARNING: Character expression will be truncated when assigned to character column CAC.

NOTE: Statement transforms to:

insert

into WORK.ACCUM(ACCUM.YEAR, ACCUM.MONTH, ACCUM.REGION, ACCUM.CAC, ACCUM.BENCAT, ACCUM.UWNONUW, ACCUM.AMT)

select A.YEAR, A.MONTH, A.REGION, A.CAC, 'T', COALESCE(B.UWNONUW, 'U'), A.GLAMT

from WORK.HSCIIIW1 A left outer join WORK.HSCIIT46 B on A.ACCOUNT = B.ACCOUNT

where A.ACCOUNT like '5%' and A.ACCOUNT not in

( select HSCIIT46.ACCOUNT

from WORK.HSCIIT46

where HSCIIT46.FLAG1 = 'X'

) and (A.YEAR = 2014) and (A.MONTH = 10);

NOTE: 5 rows were inserted into WORK.ACCUM.

Re: SAS warning message

DBailey — Thu, 30 Oct 2014 20:22:03 GMT

What is the length of ACCUM.CAC? Suppose it is 5. Then you would use

insert

into WORK.ACCUM(ACCUM.YEAR, ACCUM.MONTH, ACCUM.REGION, ACCUM.CAC, ACCUM.BENCAT, ACCUM.UWNONUW, ACCUM.AMT)

select A.YEAR, A.MONTH, A.REGION, substr(A.CAC,1,5) length=5, 'T', COALESCE(B.UWNONUW, 'U'), A.GLAMT

from WORK.HSCIIIW1 A left outer join WORK.HSCIIT46 B on A.ACCOUNT = B.ACCOUNT

where A.ACCOUNT like '5%' and A.ACCOUNT not in

( select HSCIIT46.ACCOUNT

from WORK.HSCIIT46

where HSCIIT46.FLAG1 = 'X'

) and (A.YEAR = 2014) and (A.MONTH = 10);

Re: SAS warning message

stat_sas — Thu, 30 Oct 2014 20:24:16 GMT

CAC may be of different length in WORK.ACCUM, WORK.HSCIIIW1 and WORK.HSCIIT46.

Re: SAS warning message

ettikala — Thu, 30 Oct 2014 20:26:36 GMT

Thanks Bailey for your response.

No it is 3... also would like to share we are facing this warning only after SAS 9.4 upgrade... earlier SAS 9.1 didn't report any warning as asuch.

Also here is the table info.

CREATE TABLE WORK.ACCUM /* REGIONAL */

(YEAR NUM FORMAT=4.0,

MONTH NUM FORMAT=2.0,

REGION CHAR(02) ,

CAC CHAR(03) ,

BENCAT CHAR(01) ,

UWNONUW CHAR(01) ,

AMT NUM FORMAT=16.6)

;

Regards,

Sreekanth

Re: SAS warning message

ettikala — Thu, 30 Oct 2014 20:33:16 GMT

The length is same in all the tables.

Re: SAS warning message

DBailey — Thu, 30 Oct 2014 20:33:17 GMT

We saw this as well with the upgrade to 9.4. I suppose we should have always ensured consistent data typing...but now, the warnings trigger LSF to consider the jobs to have non-normal ends.

Re: SAS warning message

DBailey — Thu, 30 Oct 2014 20:34:08 GMT

So if it is 3, then you would use

substr(A.CAC,1,3) length=3,

Re: SAS warning message

ettikala — Thu, 30 Oct 2014 20:35:20 GMT

Bailey,

Is it possible to bypass ?

Re: SAS warning message

DBailey — Thu, 30 Oct 2014 20:42:13 GMT

bypass what? I don't know of a way to suppress the warning.

Re: SAS warning message

SASKiwi — Thu, 30 Oct 2014 21:35:51 GMT

So the LENGTH option didn't remove the warning? What happens if you just use A.CAC length = 3?

If all else fails you can always use a DATA step instead of SQL although this is a fairly drastic workaround.

Also since it is a change of behaviour between SAS versions I would be tempted to track this to SAS Support - I did a quick search of SAS Notes and couldn't find any note on this.

Re: SAS warning message

ettikala — Thu, 30 Oct 2014 22:39:21 GMT

I meant supress the warning.

Re: SAS warning message

SASKiwi — Thu, 30 Oct 2014 23:32:01 GMT

Suppress, bypass, remove, I think we are meaning the same thing - we want no warning in the LOG. What happens if you just use A.CAC length = 3 (no SUBSTR function)?

Re: SAS warning message

Kurt_Bremser — Fri, 31 Oct 2014 12:19:17 GMT

Be happy that you now get a warning that notifies you of "unclean" data. Make sure that the input data sets have corresponding attributes before doing the join.