DATA Step, Macro, Functions and more

SAS Bulkload to ORACLE.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

SAS Bulkload to ORACLE.

Hi,

I am writting the content of SAS dataset to Oracle using Bulkload.
After loaing the data to Oracle table I observed that the character columns are having the trailing blanks.

Is there any bulkload option I can use to remove the trailing blanks?

Refer the below code that I was using for Bulkload.

 

proc append base=olaptable(BULKLOAD=YES
         BL_LOG="/sas/bl_lltemp.log" 
         BL_CONTROL="/sas/bl_fcst_results_stg0_0.ctl"
         BL_DATAFILE="/sas//bl_datafile.dat"
         BL_BADFILE="/sas//bl_data.bad" 
         BL_OPTIONS='ERRORS=50'
         BL_DISCARDFILE="/sas//bl_datadsc.dsc"
         BL_PRESERVE_BLANKS=YES 
         bl_return_warnings_as_errors=yes 
         )      
     data=sasorac.sasds1;

  run;

 

Quick Help is highly appreciated.

 

Thanks,
Kiran.


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 5,437

Re: SAS Bulkload to ORACLE.

Posted in reply to kumargupt

"CAUTION:

 When this option is set to YES, any trailing blank spaces are also inserted. For this reason, use this option with caution. It is recommended that you set this option to YES only for CHAR columns. Do not set this option to YES for VARCHAR2 columns because trailing blank spaces are significant in VARCHAR2 columns."
 
I guess that there is no real work around, unless to specify it for specific columns rather than the whole table. Or as some post processing to BL file or loaded Oracle table, but that would obviously take away some of the benefits of using bulk load.
Data never sleeps

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 5,437

Re: SAS Bulkload to ORACLE.

Posted in reply to kumargupt

"CAUTION:

 When this option is set to YES, any trailing blank spaces are also inserted. For this reason, use this option with caution. It is recommended that you set this option to YES only for CHAR columns. Do not set this option to YES for VARCHAR2 columns because trailing blank spaces are significant in VARCHAR2 columns."
 
I guess that there is no real work around, unless to specify it for specific columns rather than the whole table. Or as some post processing to BL file or loaded Oracle table, but that would obviously take away some of the benefits of using bulk load.
Data never sleeps
Occasional Contributor
Posts: 16

Re: SAS Bulkload to ORACLE.

Thanks for the feedback.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 99 views
  • 0 likes
  • 2 in conversation