PROC SQL Suppress WARNING during BULKLOAD insert

HariPrasad2005 — Wed, 23 Aug 2017 14:15:19 GMT

Hi All,

I am facing this wierd issue with PROC SQL, The below code is working fine in SAS 9.3 Windows server, We are migarting to SAS 9.4 Linux. The same code is producing WARNING and the warning seems inappropriate and its repeating for every single record it inserts. Which is causing the log file size to increase exponentically

If anyone else faced similar issue and found a solution kindly share. Thanks in advance

MPRINT(APPEND2NETEZZATABLE):   Proc SQL NoPrint ;
MPRINT(APPEND2NETEZZATABLE):   Select case when strip(upcase(name)) eq strip(upcase("yearmonth")) and type ne 'char' then
strip("201706 as yearmonth") when strip(upcase(name)) eq strip(upcase("yearmonth")) and type eq 'char' then strip("'201706' as
yearmonth") when put(name,$a2ntfmt.) ne 'Y' then case type when 'char' then catx(' ',"''",CatS('Length=',Length),"As",name) else
catx(' ',". As",name) end else case type when 'char' then catx(' ',name,CatS('Length=',Length),"As",name) else name end end as name
into :A2NT_SRC_COLUMNS Separated by ',' From A2NT_TGT_COLUMNS ;
MPRINT(APPEND2NETEZZATABLE):   Insert Into NZ.CLASS ( BULKLOAD=YES BL_DELIMITER=128
BL_DATAFILE="/apps/sas/sasdata/svc/rsk/align_code/logs/bulkload/CLASS1819056786.dat" BL_OPTIONS="fillrecord true ignorezero true
CtrlChars true maxerrors 1 logdir '/apps/sas/sasdata/svc/rsk/align_code/logs/bulkload/' " BL_USE_PIPE=NO BL_DELETE_DATAFILE=YES )
Select 201706 as yearmonth,NAME Length=8 As NAME,SEX Length=1 As SEX,AGE,HEIGHT,WEIGHT From SASHELP.CLASS ;
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column NAME
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
WARNING: Character expression will be truncated by the engine when assigned to character column SEX
NOTE: 19 rows were inserted into NZ.CLASS.

Re: PROC SQL Suppress WARNING during BULKLOAD insert

RW9 — Wed, 23 Aug 2017 14:22:06 GMT

Erm, not sure why your putting SAS length statements in there:

Select 201706 as yearmonth,NAME Length=8 As NAME,SEX Length=1 As SEX,AGE,HEIGHT,WEIGHT From SASHELP.CLASS ;

Assuming that table your inserting into is long enough to hold the data then:

select 201706,NAME,SEX,AGE,HEIGHT,WEIGHT from SASHELP.CLASS;

Should work fine. Its hard to tell as cannot see the table this is being inserted into.

Re: PROC SQL Suppress WARNING during BULKLOAD insert

HariPrasad2005 — Wed, 23 Aug 2017 14:25:20 GMT

The initial code was with out the length option. I thought setting the length of the column matching with the target table length will suppress the warning. But no luck. The WARNING still pops up for every record 😞

Re: PROC SQL Suppress WARNING during BULKLOAD insert

HariPrasad2005 — Wed, 23 Aug 2017 14:26:52 GMT

The Target table structure
CREATE TABLE CLASS
(
YEARMONTH INTEGER NOT NULL,
NAME CHARACTER VARYING(8),
SEX CHARACTER(1),
AGE DOUBLE PRECISION,
HEIGHT DOUBLE PRECISION,
WEIGHT DOUBLE PRECISION
)
DISTRIBUTE ON RANDOM;

Re: PROC SQL Suppress WARNING during BULKLOAD insert

LinusH — Thu, 24 Aug 2017 15:42:29 GMT

Are your shure that nothing else have changed, like global system/libname options, dbms upgrades, target table definition...?

Re: PROC SQL Suppress WARNING during BULKLOAD insert

HariPrasad2005 — Thu, 24 Aug 2017 16:12:18 GMT

Thanks everyone, There is a hot fix for this issue. I think this issue is specifically related to NETEZZA database BULKLOAD

http://support.sas.com/kb/60/273.html

-Hari Prasad T

topic Re: PROC SQL Suppress WARNING during BULKLOAD insert in SAS Procedures

PROC SQL Suppress WARNING during BULKLOAD insert

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Re: PROC SQL Suppress WARNING during BULKLOAD insert