topic SAS Bulkload to ORACLE. in SAS Programming

SAS Bulkload to ORACLE.

kumargupt — Wed, 27 Sep 2017 12:53:00 GMT

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.

Re: SAS Bulkload to ORACLE.

LinusH — Wed, 27 Sep 2017 13:17:02 GMT

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

Re: SAS Bulkload to ORACLE.

kumargupt — Mon, 30 Oct 2017 16:04:42 GMT

Thanks for the feedback.