Help using Base SAS procedures

problem on "proc append" with bulkload option in SAS9.2

Reply
Occasional Contributor
Posts: 8

problem on "proc append" with bulkload option in SAS9.2

Hi all,

Previously in SAS 9.1, I used  "proc append" to bulkload a UTF encoding dataset to Oracle DB.  The sqlloader is generated correctly as the position is same as the length of column ( please see the highlighted column "FILE_NM" POSITION(10:69) )

PROC APPEND BASE = EDR_ORA.ED_BWHS_M_ACCT_APPLT

( DBMAX_TEXT=30000 BULKLOAD = YES BL_DELETE_DATAFILE = NO BL_OPTIONS="ERRORS=50 ,
ROWS=100000" BL_CONTROL= MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.ctl"
BL_DATAFILE= "MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.bat"
BL_BADFILE = "MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.blbad"
BL_LOG = "MI_BWHS_003_02.sqllog.tmp"

BL_DISCARDFILE = "MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.bldsc"
SASDATEFMT= (BATCH_DT = 'DATE9.' APPLICATION_DT='DATE9.' OUTCOME_DT='DATE9.'))
FORCE DATA = QA_BWHS.M_5830_ACCT_APPLT_201111 ;

RUN;

-- sqolader ---

OPTIONS (DIRECT=TRUE ,ERRORS=50 , ROWS=100000)

LOAD DATA INFILE "MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.bat"

"FIX 668                                                         "

INTO TABLE ED_BWHS_M_ACCT_APPLT APPEND (

"BATCH_DT" POSITION(1:9)  NULLIF "BATCH_DT"=BLANKS "TO_DATE(:\"BATCH_DT\",'DDMONYYYY'

,'NLS_DATE_LANGUAGE=American')"

,"FILE_NM" POSITION(10:69) CHAR,...

Currently In SAS 9.2, I used  the same "proc append" to bulkload a UTF encoding dataset to Oracle DB. The sqlloader is generated incorrectly as the position is multiplied 4 times of the length of column ( please see the highlighted column - "FILE_NM" POSITION(10:249))!!!

-- sqolader ---

OPTIONS (DIRECT=TRUE ,ERRORS=50 , ROWS=100000)

LOAD DATA INFILE "MI_BWHS_003_02_ED_BWHS_M_ACCT_APPLT_20111130.bat"

"FIX 1928                                                        "

INTO TABLE ED_BWHS_M_ACCT_APPLT APPEND (

"BATCH_DT" POSITION(1:9)  NULLIF "BATCH_DT"=BLANKS "TO_DATE(:\"BATCH_DT\",'DDMONYYYY',

'NLS_DATE_LANGUAGE=American')",

"FILE_NM" POSITION(10:249) CHAR ,....

Can anyone help this problem?

Thanks a lot,

Johnson

Ask a Question
Discussion stats
  • 0 replies
  • 507 views
  • 0 likes
  • 1 in conversation