SAS warning message

[ettikala]

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.

[DBailey]

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);

[ettikala]

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

[DBailey]

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.

[ettikala]

Bailey,

Is it possible to bypass ?

[DBailey]

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

[ettikala]

I meant supress the warning.

[SASKiwi]

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)?

[stat_sas]

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

[ettikala]

The length is same in all the tables.

[DBailey]

So if it is 3, then you would use

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

[SASKiwi]

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.

[Kurt_Bremser]

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.