BookmarkSubscribeRSS Feed
ettikala
Calcite | Level 5

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.

12 REPLIES 12
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

Bailey,

Is it possible to bypass ?

DBailey
Lapis Lazuli | Level 10

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

ettikala
Calcite | Level 5

I meant supress the warning.

SASKiwi
PROC Star

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
Ammonite | Level 13

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

ettikala
Calcite | Level 5

The length is same in all the tables.

DBailey
Lapis Lazuli | Level 10

So if it is 3, then you would use

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

SASKiwi
PROC Star

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2613 views
  • 1 like
  • 5 in conversation