BookmarkSubscribeRSS Feed
droog
Calcite | Level 5

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;

7 REPLIES 7
ballardw
Super User

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

droog
Calcite | Level 5

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 ?

dkb
Quartz | Level 8 dkb
Quartz | Level 8

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.

PGStats
Opal | Level 21

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

insert into aSQL

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

PG

PG
droog
Calcite | Level 5

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

PGStats
Opal | Level 21

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.

PG

PG
dkb
Quartz | Level 8 dkb
Quartz | Level 8

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.'

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
  • 7 replies
  • 5419 views
  • 0 likes
  • 4 in conversation