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

[sspkmnd]

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

[HB]

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.

[sspkmnd]

I need to use update with a subquery.

[art297 · Accepted solution]

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