Help using Base SAS procedures

PROC SQL Suppress WARNING during BULKLOAD insert

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

PROC SQL Suppress WARNING during BULKLOAD insert

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.

 

 


Accepted Solutions
Solution
‎08-24-2017 12:13 PM
New Contributor
Posts: 4

Re: PROC SQL Suppress WARNING during BULKLOAD insert

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Posted in reply to HariPrasad2005

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. 

New Contributor
Posts: 4

Re: PROC SQL Suppress WARNING during BULKLOAD insert

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 Smiley Sad

New Contributor
Posts: 4

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Posted in reply to HariPrasad2005
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;
Super User
Posts: 5,876

Re: PROC SQL Suppress WARNING during BULKLOAD insert

Posted in reply to HariPrasad2005
Are your shure that nothing else have changed, like global system/libname options, dbms upgrades, target table definition...?
Data never sleeps
Solution
‎08-24-2017 12:13 PM
New Contributor
Posts: 4

Re: PROC SQL Suppress WARNING during BULKLOAD insert

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

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 234 views
  • 0 likes
  • 3 in conversation