topic Re: SQL truncation warning should NOT appear in SAS Programming

SQL truncation warning should NOT appear

droog — Tue, 22 Jul 2014 21:04:46 GMT

Because the query includes the MD5 and one of the CAT* functions ( which default to $200 ), the INSERT below generates the warning below, even though the SUBSTR is supplying the length required. SAS Tech Support suggested I use the option NOWARN but it's hardly tenable to turn off ALL warnings for something that SQL should ignore on its own. Note that the DATA step does not throw a similar complaint.

Many installations will not allow production processes to throw warnings - let's see if we can get this fixed.

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

proc sql;

create table asql as

select a.*

, md5('blah') as hash_key length=16

, catx('|',name,sex) as cats_var length=32

from sashelp.class ( obs = 0 ) a

;

insert into asql

select a.*

, substr(md5(name || put(height,z3.) || put(weight,z6.2) || sex),1,16) as hash_key length=16 format=$hex32.

, substr(catx('|', name || put(height,z3.) || put(weight,z6.2) || sex),1,32) as cats_var length=32

from sashelp.class a

;

quit;

Re: SQL truncation warning should NOT appear

ballardw — Tue, 22 Jul 2014 23:17:31 GMT

Running just this part of the code:

proc sql;

insert into asql

select a.*

, substr(md5(name || put(height,z3.) || put(weight,z6.2) || sex),1,16) as hash_key length=16 format=$hex32.

, substr(catx('|', name || put(height,z3.) || put(weight,z6.2) || sex),1,32) as cats_var length=32

from sashelp.class a

;

quit;

I get:

NOTE: Invalid argument 3 to function SUBSTR. Missing values may be generated.

and not your warning with the second insert. The above may be masked because your second insert is in contention with the result of the invalid. Since cats_var has no | delimiter in the result I suggest you try using CATX the way it is intended

catx('|', name, put(height,z3.) , put(weight,z6.2) , sex) as using the || operator you generate one string and since there isn't a second there is no | inserted anywhere if your goal was to have a string like

Jane|060|084.50|F

Re: SQL truncation warning should NOT appear

droog — Wed, 23 Jul 2014 00:11:33 GMT

Sorry, my sloppiness with copy / paste from two separate code sections has caused confusion. Yes, of course the CATX should have commas betwixt the arguments, not the double pipe

But please see the subject line, why is the truncation WARNING appearing where it ought not ? i.e. the SUBSTR clearly sets the length in DATA step, shouldn't it do the same thing in SQL ?

Re: SQL truncation warning should NOT appear

dkb — Wed, 23 Jul 2014 01:03:43 GMT

Defining a view and using that to drive the update gets rid of the warnings...

proc sql;

create view aview as

select a.*

, substrn(md5(name || put(height,z3.) || put(weight,z6.2) || sex),1,16) length=16 as hash_key format=$hex32.

, substrn(catx('|', name, put(height,z3.), put(weight,z6.2), sex),1,32) length=32 as cats_var

from sashelp.class a

;

insert into asql

select a.*

from aview a

;

quit;

Hope this circumvention is helpful.

Re: SQL truncation warning should NOT appear

PGStats — Wed, 23 Jul 2014 02:02:39 GMT

To build on 's suggestion, you can use a subquery, such as

insert into aSQL

select * from (select md5(d) length=20 from test);

Re: SQL truncation warning should NOT appear

droog — Wed, 23 Jul 2014 11:48:24 GMT

Thanks dkb and PGStats, both suggestions work. Interesting that SQL "loses sight" of the apparent truncation if a sub-query or view is employed.

Re: SQL truncation warning should NOT appear

PGStats — Wed, 23 Jul 2014 14:32:54 GMT

I think there is a certain logic to it. When you create a new table and set the length of a field with LENGTH=, it is your choice, it can be safely assumed that you know about the length of that field.No warning needed there. When, on the other hand, you insert records into another table, the field lengths are defined elsewhere and you might not be aware of their true value. Thus the warning.

Re: SQL truncation warning should NOT appear

dkb — Wed, 23 Jul 2014 21:59:36 GMT

Remember that SUBSTR can be called with a variable as the third term, in which case there is no way of knowing at compile time whether the field will be truncated at run time - it's going to be data dependent. I think we can infer that the compiler has been written to handle that as the general case, and hasn't had a special override added to say 'but if the user has specified a literal in position 3, check the table definition because we might not need the warning message after all.'