SAS/ACCESS^® Interface to Oracle adds trailing blanks at the end of a variable when you bulk load data to the Oracle database. The SAS^® System handles reading variables of type VARCHAR this way because SAS does not have a variable-length data type. Therefore, variable values that are less than the full length of the variable are padded with blanks. For example, suppose a variable is defined with a length of 10 and you store the value ABC in the variable. What the software actually stores is the value ABC followed by seven blanks.

To remove the trailing blanks at the end of a variable, use any of the following options:

• Use the SAS TRIMN() function after data is loaded. The TRIMN() function is passed to the Oracle database as the RTRIM() function. This conversion removes trailing blanks, but it does require additional coding and an extra step.
• Do not use bulk loading. If you use the BULKLOAD=NO option, then no trailing blanks occur.
• Strip the blanks by either modifying or changing the control file that is created by SAS for use by the Oracle SQL*Loader.

Example of a Control File

OPTIONS (DIRECT=TRUE , ERRORS=1000000) LOAD DATA INFILE "amy_bulk.dat" "FIX 38" INTO TABLE AMY_BULK APPEND ( "ID" POSITION(1:8) DOUBLE NULLIF "ID"=BLANKS , "INFO" POSITION(9:38) CHAR "decode(:info,null,' ')" ) Table definition: Name Null? Type ----------------------------------------- -------- -------------- ID NUMBER INFO NOT NULL VARCHAR2(30)

Code examples that illustrate how to avoid the trailing blanks:

1. In this example, the NOT NULL columns use the database default values.
   /* Sample_20100816_1: Oracle bulk loading, without trailing blanks, into NOT NULL columns. */ /* Use BULKLOAD=YES and DROP=INFO to remove columns so that the columns use database default values. */ /* Variables need to be modified. */ %let myuser=user-id; %let mypass=password; %let mypath=path-name; /* End of modified variables. */ /* Generic code. */ data test; id=1; info=''; output; id=2; info=' '; output; run; libname x oracle user=&myuser pass=&mypass path=&mypath; proc append base=x.amy_bulk(bulkload=yes bl_direct_path=no drop=info) data=test(drop=info); run; /* End of sample_20100816_1. */
2. This example loads the data and then reloads the rejected rows (assuming that there are not too many rejected rows because of NOT NULL column issues).

      /* Sample_20100816_2: Oracle bulk loading, without trailing blanks, into NOT NULL columns. */
      /* Use BULKLOAD=YES and reload the rejected rows.                                          */
   
   
      /* Variables need to be modified. */
   %let myuser=user-id;
   %let mypass=password;
   %let mypath=path-name;
      /* End of modified variables. */
   
   
      /* Generic code */
   data test;
      length info $30.;
      id=21; info='good data'; output;
      id=22; info=''; output;
      id=23; info='     '; output;
   run;
   
   libname x oracle user=&myuser pass=&mypass path=&mypath;
   
      /* You might need to adject the file path depending on the platform used */
   proc append base=x.amy_bulk(bulkload=yes bl_datafile="c:/temp/amy_bulk.dat" bl_delete_datafile=no)
     data=test;
   run;
   
   /* End of sample_20100816_2*/