BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HariPrasad2005
Fluorite | Level 6

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
HariPrasad2005
Fluorite | Level 6

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

HariPrasad2005
Fluorite | Level 6

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 😞

HariPrasad2005
Fluorite | Level 6
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;
LinusH
Tourmaline | Level 20
Are your shure that nothing else have changed, like global system/libname options, dbms upgrades, target table definition...?
Data never sleeps
HariPrasad2005
Fluorite | Level 6

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1772 views
  • 0 likes
  • 3 in conversation