HI,
While creating the SAS DI jobs to read the data from external files and to populate the SAS datastes we are facing the issues below…
1. Trialing blanks replaced with junk values for characteristic columns
2. When we use informat as s370fpdw. for Numerical columns that allow null values, SAS throws an error saying invalid data for such columns. For time being we have overridden the system option “ERRORS =” to skip the errors.
We have used ftp access method to read the data from external files exist in Z/OS.
We have been creating the SAS DI jobs in the windows environment to read the files exist in Z/OS environment. As you know we should convert the ebcdic data into ascii data since we create the jobs in windows environment.
You may refer the external file structure below…
000001 LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
000002 SCHEMA.BS_MANDATE
000003 (
000004 BS_MANDATE_NO POSITION( 1: 6)
000005 DECIMAL ,
000006 CRED_CG_ID POSITION( 7: 12)
000007 DECIMAL ,
000008 CRED_CUST_NO POSITION( 13 )
000009 VARCHAR ,
000010 DBTR_NO POSITION( 45: 50)
000011 DECIMAL ,
000012 DBTR_ACCT_CATEGORY_ID POSITION( 51: 56)
000013 DECIMAL ,
000014 DBTR_REG_NO POSITION( 57: 59)
000015 DECIMAL ,
000016 DBTR_ACCT_NO POSITION( 60: 65)
000017 DECIMAL ,
000018 DBTR_IBAN_NO POSITION( 66 )
000019 VARCHAR
000020 NULLIF( 102)='?',
000021 DELV_ID POSITION( 103: 108)
000022 DECIMAL
000023 NULLIF( 109)='?',
000024 CREATED_BY POSITION( 110 )
000025 CHAR( 4)
000026 NULLIF( 114)='?',
000027 REPORTING_IDENT POSITION( 115 )
000028 VARCHAR
000029 NULLIF( 133)='?',
000030 START_DT POSITION( 134 )
000031 DATE EXTERNAL( 10) ,
000032 VAL_START_DT POSITION( 144 )
000033 DATE EXTERNAL( 10) ,
000034 VAL_END_DT POSITION( 154 )
000035 DATE EXTERNAL( 10) ,
000036 UPDATE_BY POSITION( 164 )
000037 VARCHAR ,
000038 UPDATE_TS POSITION( 206 )
000039 TIMESTAMP EXTERNAL( 26)
000040 )
Please refer the code below that the SAS DI job has been generated.
data work.etls_W5TR1SOO;
infile "'A1.Z00D.ML1. MUNL02.D100219'" Ftp Host='HOST' Port=2
firstobs = 1
BInary RECFM=F lrecl=231 blocksize=32000 debug USER='Use12r' PASS='{sasenc}2232812D2927A8E029DA';
attrib BS_MANDATE_NO length = 6 informat = s370fpd6.;
attrib CRED_CG_ID length = 6 informat = s370fpd6.;
attrib CRED_CUST_NO length = $32 informat = $ebcdic32.;
attrib DBTR_NO length = 6 informat = s370fpd6.;
attrib DBTR_ACCT_CATEGORY_ID length = 6 informat = s370fpd6.;
attrib DBTR_REG_NO length = 3 informat = s370fpd3.;
attrib DBTR_ACCT_NO length = 6 informat = s370fpd6.;
attrib DBTR_IBAN_NO length = $36 informat = $ebcdic36.;
attrib NULL_IF_IBAN length = $1 informat = $ebcdic1.;
attrib DELV_ID length = 6 informat = s370fpd6.;
attrib NULL_IF_DELV length = $1 informat = $ebcdic1.;
attrib CREATED_BY length = $4 informat = $ebcdic4.;
attrib NUL_IF_2 length = $1 informat = $ebcdic1.;
attrib REPORTING_IDENT length = $16 informat = $ebcdic18.;
attrib NULL_IF_ID length = $1 informat = $ebcdic1.;
attrib START_DT length = $10 informat = $ebcdic10.;
attrib VAL_START_DT length = $10 informat = $ebcdic10.;
attrib VAL_END_DT length = $10 informat = $ebcdic10.;
attrib UPDATE_BY length = $42 informat = $ebcdic42.;
attrib UPDATE_TS length = $26 informat = $ebcdic26.;
input @ 1 BS_MANDATE_NO s370fpd6.
@ 7 CRED_CG_ID s370fpd6.
@ 13 CRED_CUST_NO $ebcdic32.
@ 45 DBTR_NO s370fpd6.
@ 51 DBTR_ACCT_CATEGORY_ID s370fpd6.
@ 57 DBTR_REG_NO s370fpd3.
@ 60 DBTR_ACCT_NO s370fpd6.
@ 66 DBTR_IBAN_NO $ebcdic36.
@ 102 NULL_IF_IBAN $ebcdic1.
@ 103 DELV_ID $ebcdic6.
@ 109 NULL_IF_DELV $ebcdic1.
@ 110 CREATED_BY $ebcdic4.
@ 114 NUL_IF_2 $ebcdic1.
@ 117 REPORTING_IDENT $ebcdic18.
@ 133 NULL_IF_ID $ebcdic1.
@ 134 START_DT $ebcdic10.
@ 144 VAL_START_DT $ebcdic10.
@ 154 VAL_END_DT $ebcdic10.
@ 164 UPDATE_BY $ebcdic42.
@ 206 UPDATE_TS $ebcdic26.;
run;
When we view data from SAS dataset, it displays data for characteristic columns properly, but when we tried to print data using the proc print option; we come to know the issue that trailing blanks are replaced with junk values for characteristic columns.
There is a field named DELV_ID defined as numeric and it allows nulls, we use the informat as s370fpd6. As we did for other numerical columns, but when we run the job, it throws an error saying invalid data, since z/os dataset replaced nulls with “?”
You may refer the generated log below…
NOTE: Invalid data for DELV_ID in line 16 103-108.
16 CHAR .....<... .L..èÄäððððððððððñù.................7sPœ........\..R9W\...........................
ZONE 00001300021400ECEFFFFFFFFFFFF000000000000000093759000001005005355000000000000000000000000000
NUMR 00007C00001C0F84400000000001900000000000000010730C00000C31C00297C000000000000000000000000000
93 .........o......oÃÙÅÄ...................oðñKðöKòððùðñKðöKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F39540000000000000000000F01B06B200901B06B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`òô`ñóKôôKõõK÷ö÷ððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004024013B44B55B767000
BS_MANDATE_NO=173 CRED_CG_ID=20114 CRED_CUST_NO= _YDU000000000019 DBTR_NO=1903773509
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=3015 DBTR_ACCT_NO=5239575
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CRED NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.06.2009
VAL_START_DT=01.06.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-24-13.44.55.767000 _ERROR_=1 _N_=16
NOTE: Invalid data for DELV_ID in line 18 103-108.
18 CHAR .....\... .<..ðøð÷ùððððñððù÷ð...................&\........œ.....\...........................
ZONE 00001500020300FFFFFFFFFFFFFFF000000000000000011025000001009000005000000000000000000000000000
NUMR 00007C00001C0F08079000010097000000000000000000006C00000C87C00109C000000000000000000000000000
93 .........o......oÃÙÅÄ...................oðñKðõKòððùðñKðõKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F39540000000000000000000F01B05B200901B05B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`ñô`ññKñùKõôKøöðððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004014011B19B54B860000
BS_MANDATE_NO=175 CRED_CG_ID=20013 CRED_CUST_NO= _080790000100970 DBTR_NO=101000265
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=8079 DBTR_ACCT_NO=100095
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CRED NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.05.2009
VAL_START_DT=01.05.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-14-11.19.54.860000 _ERROR_=1 _N_=18
NOTE: Invalid data for DELV_ID in line 19 103-108.
19 CHAR .....l... .<..ðøð÷ùððððñððùöò.................d …|........œ.... ,...........................
ZONE 00001600020300FFFFFFFFFFFFFFF000000000000000006287000001009000022000000000000000000000000000
NUMR 00007C00001C0F08079000010096200000000000000010405C00000C87C00100C000000000000000000000000000
93 .........o......oÃÙÅÄ...................oðñKðõKòððùðñKðõKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F39540000000000000000000F01B05B200901B05B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`ñô`ññKòõKñòKùù÷ððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004014011B25B12B997000
BS_MANDATE_NO=176 CRED_CG_ID=20013 CRED_CUST_NO= _080790000100962 DBTR_NO=1006420857
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=8079 DBTR_ACCT_NO=100202
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CRED NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.05.2009
VAL_START_DT=01.05.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-14-11.25.12.997000 _ERROR_=1 _N_=19
NOTE: Invalid data for DELV_ID in line 20 103-108.
20 CHAR .....|... .l..ççÑððððððððððñø................A.aBœ........œ....)<...........................
ZONE 00001700020600EEDFFFFFFFFFFFF000000000000000041649000001009000023000000000000000000000000000
NUMR 00007C00009C0F77100000000001800000000000000021012C00000C87C00109C000000000000000000000000000
93 .........o......oÃÙÅÄ...................oðñKðöKòððùðñKðöKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F39540000000000000000000F01B06B200901B06B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`ò÷`ñôKñõKõöKôóùððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004027014B15B56B439000
BS_MANDATE_NO=177 CRED_CG_ID=20096 CRED_CUST_NO= _XXJ000000000018 DBTR_NO=2411061429
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=8079 DBTR_ACCT_NO=100293
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CRED NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.06.2009
VAL_START_DT=01.06.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-27-14.15.56.439000 _ERROR_=1 _N_=20
NOTE: Invalid data for DELV_ID in line 21 103-108.
21 CHAR .....Œ... .œ..ççÑððððððððððóô................pv.)<........œ....d............................
ZONE 00001800020900EEDFFFFFFFFFFFF000000000000000077123000001009000060000000000000000000000000000
NUMR 00007C00001C0F77100000000003400000000000000000649C00000C87C00104C000000000000000000000000000
93 .........o......oÃÙÅÄ...................oðñKðöKòððùðñKðöKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F39540000000000000000000F01B06B200901B06B200931B12B99990367214495B27D025B235
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--
185 .....................òððù`ðô`ò÷`ñ÷KôøKôðKøñùððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004027017B48B40B819000
BS_MANDATE_NO=178 CRED_CG_ID=20019 CRED_CUST_NO= _XXJ000000000034 DBTR_NO=707614293
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=8079 DBTR_ACCT_NO=100640
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CRED NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.06.2009
VAL_START_DT=01.06.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-27-17.48.40.819000 _ERROR_=1 _N_=21
NOTE: Invalid data for DELV_ID in line 22 103-108.
22 CHAR .....œ... .œ..ççÑððððððððððóõ...................e<........\.....|...........................
ZONE 00001900020900EEDFFFFFFFFFFFF000000000000000000063000001005001007000000000000000000000000000
NUMR 00007C00001C0F77100000000003500000000000000001005C00000C31C00004C000000000000000000000000000
93 .........o......oÃÄÅÂ...................oðñKðöKòððùðñKðöKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CCCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F34520000000000000000000F01B06B200901B06B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`ò÷`ñ÷KõñKóøKøðøððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004027017B51B38B808000
BS_MANDATE_NO=179 CRED_CG_ID=20019 CRED_CUST_NO= _XXJ000000000035 DBTR_NO=10000653
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=3015 DBTR_ACCT_NO=1000047
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CDEB NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.06.2009
VAL_START_DT=01.06.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-27-17.51.38.808000 _ERROR_=1 _N_=22
NOTE: Invalid data for DELV_ID in line 23 103-108.
ERROR: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
23 CHAR ......... .L..ççÑððððððððððóù...................VL........œ....f\...........................
ZONE 00001000020400EEDFFFFFFFFFFFF000000000000000000054000001009000065000000000000000000000000000
NUMR 00008C00002C0F77100000000003900000000000000001006C00000C87C00106C000000000000000000000000000
93 .........o......oÃÄÅÂ...................oðñKðöKòððùðñKðöKòððùóñKñòKùùùù..¦—¢.„”‰•kÂ×mðòõkâÃÅ
ZONE 00000000060000006CCCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC
NUMR 000000000F000000F34520000000000000000000F01B06B200901B06B200931B12B99990367214495B27D025B235
185 .....................òððù`ðô`ò÷`ñøKðñKñ÷Kù÷õððð
ZONE 000000000000000000000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 0000000000000000000002009004027018B01B17B975000
BS_MANDATE_NO=180 CRED_CG_ID=20024 CRED_CUST_NO= _XXJ000000000039 DBTR_NO=10000564
DBTR_ACCT_CATEGORY_ID=1 DBTR_REG_NO=8079 DBTR_ACCT_NO=100665
DBTR_IBAN_NO= NULL_IF_IBAN=? DELV_ID=. NULL_IF_DELV=?
CREATED_BY=CDEB NUL_IF_2= REPORTING_IDENT= NULL_IF_ID=? START_DT=01.06.2009
VAL_START_DT=01.06.2009 VAL_END_DT=31.12.9999 UPDATE_BY= _wpsadmin,BP_025,SCE
UPDATE_TS=2009-04-27-18.01.17.975000 _ERROR_=1 _N_=23
NOTE: <<< 250 Transfer completed successfully.
NOTE: >>> QUIT
NOTE: 53 records were read from the infile "'A1.Z00D.ML1. MUNL02.D100219'".
NOTE: The data set WORK.W5TR1SOO has 53 observations and 20 variables.
NOTE: DATA statement used (Total process time):
real time 0.73 seconds
cpu time 0.20 seconds
For time being we have overridden the default system option ERRORS = 20 as ERRORS = 100; in the pre processor, but we worried that this option may skip other valid errors.
Could you please help us to resolve the above issues?
Kindly revert, if you need more details…
Thanks and Regards,
AnjiReddy.