DATA Step, Macro, Functions and more

PROC SQL: WARNING: Character expression will be truncated when assigned to character column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

PROC SQL: WARNING: Character expression will be truncated when assigned to character column

data da;
    length a $20;
    a = 'asd';
run;

proc sql noprint;
    update da set a = (select cats(max(age), '.') from sashelp.class);
    update da set a = (select substrn(cats(max(age), '.'), 1, 20) from sashelp.class);
    update da set a = (select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class);
    update da set a = substrn((select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class), 1, 20);
quit;

All statements above produce a warning about truncation. Does anyone know how to get rid of them?

 

Thanks & KR


Accepted Solutions
Solution
‎01-23-2018 11:25 AM
PROC Star
Posts: 8,146

Re: PROC SQL: WARNING: Character expression will be truncated when assigned to character column

You can avoid the $200 length by NOT using the cat family of functions. e.g.:

data da;
    length a $20;
    a = 'asd';
run;

proc sql noprint;
    update da set a = (select strip(put(max(age),2.))||'.' from sashelp.class)
/*     update da set a = (select substrn(cats(max(age), '.'), 1, 20) from sashelp.class) */
/*     update da set a = (select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class) */
/*     update da set a = substrn((select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class), 1, 20) */
  ;
quit;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 266

Re: PROC SQL: WARNING: Character expression will be truncated when assigned to character column

[ Edited ]

 

 

If you do just

proc sql;
	create table a as
	select cats(max(age), '.') as myvar from sashelp.class;
quit;

by itself, and then look at the attributes of myvar you will see that it is $200, which is likely some sort of default length.

 

You can make the length of a in da longer or perhaps ALTER da with SQL.

 

 

EDIT:

 

this

data da;
    length a $220;
    a = 'asd';
run;

proc sql noprint;
    update da set a = (select cats(max(age), '.') from sashelp.class);
    update da set a = (select substrn(cats(max(age), '.'), 1, 20) from sashelp.class);
    update da set a = (select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class);
    update da set a = substrn((select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class), 1, 20);
quit;

proc sql;
	alter table da 
	modify a varchar(20); 
quit;

 

 doesn't throw any errors and may give you something to work with.

 

 

Occasional Contributor
Posts: 13

Re: PROC SQL: WARNING: Character expression will be truncated when assigned to character column

I need to use update with a subquery.
Solution
‎01-23-2018 11:25 AM
PROC Star
Posts: 8,146

Re: PROC SQL: WARNING: Character expression will be truncated when assigned to character column

You can avoid the $200 length by NOT using the cat family of functions. e.g.:

data da;
    length a $20;
    a = 'asd';
run;

proc sql noprint;
    update da set a = (select strip(put(max(age),2.))||'.' from sashelp.class)
/*     update da set a = (select substrn(cats(max(age), '.'), 1, 20) from sashelp.class) */
/*     update da set a = (select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class) */
/*     update da set a = substrn((select substrn(cats(max(age), '.'), 1, 20) length=20 from sashelp.class), 1, 20) */
  ;
quit;

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 575 views
  • 0 likes
  • 3 in conversation