Difficulties in reading the external file data in ebcdic format

[deleted_user]

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.

[Patrick]

Hi AnjiReddy.

I'm actually quite amazed that "External Files" even work with z\OS - and I like the code generated.

I can see that the raw data is transferred via ftp binary mode so that the EBCDIC/ASCII translation happens in the target system.

It looks to me as if blanks are not converted correctly. It might still look good in a viewer when blanks are translated to some whitespace character. But as it is not a blank informats will most likely treat this whitespace value different when reading (i.e. as invalide numeric value).

First I would check how the values are stored in the target system. Are they really blanks on the mainfraime (I believe this would be HEX04)?

If they are blank in the source (EBCDIC Hex04): Something with the translation must go wrong. Translation tables come to mind (trantab option, proc trantab).

You might have to investigate what translation tables are used in your target system and if they are correct. As your name doesn't sound American there might be some issues with the National Language Settings (I had already a lot of "fun" with this...).


If you're using SAS9.2 AND it's only about blanks AND you need a quick and dirty solution:

The FileReader in DIS4.2 gives you this possibility to add aditional options to the infile statement. What one could do is to "inject" here a piece of code in the following way:

; /* this ends the infile statement */
input @;
_infile_=translate(_infile_,' ','');

/* and after this DIS will generate the input statement now mapping to the already prepared input buffer */

The list of garbadge characters should be in it's hex representation as you get them currently in your variables.

HTH
Patrick

[sbb]

I see some inconsistencies with your selectively pasted SAS program code -- you have declared a SAS numeric variable however you are attempting to use a CHARACTER type INFORMAT.

I suggest you FTP binary transfer a complete data file then analyze the field/column as a SAS CHARACTER variable using PROC FREQ.

Scott Barry
SBBWorks, Inc.

[Patrick]

I missed this numeric/character inconsistency which Scott mentions.

This is sure the very first step of investigation and has to be consistent before you even start looking further.

Cheers
Patrick

[sbb]

Also, the info you shared about your "external file structure" also conflicts with the SAS-default INFORMAT (on the ATTRIB statement) - you show a mainframe packed-decimal INFORMAT S370PDw. being used however your supplemental info shows DECIMAL and not "DECIMAL PACKED".

Also, you might want to desk-check how SAS will interpret a NULL value in the numeric type data field -- you can expect to receive NOTE condition with INVALID DATA (similar to below) when you have a packed-decimal (interpreted) field with a hex-zero (null) content.

Scott Barry
SBBWorks, Inc.


1 data _null_;
2 x = input('00000000'x,s370fpd4.);
3 putlog '>diag1>' / _all_;
4 x = input('0000000F'x,s370fpd4.);
5 putlog '>diag2>' / _all_;
6 x = input('00000000'x,?? s370fpd4.);
7 putlog '>diag3>' / _all_;
8 run;

NOTE: Invalid argument to function INPUT at line 2 column 5.
>diag1>
x=. _ERROR_=1 _N_=1
>diag2>
x=0 _ERROR_=1 _N_=1
>diag3>
x=. _ERROR_=1 _N_=1
x=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 2:5
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[Patrick]

I assume the load data statements are working and correct.
Following Scott's lead I understand that for DECIMAL the appropriate informat would be S370FFw.d (http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000195467.htm).

Also you're writing that "...throws an error saying invalid data, since z/os dataset replaced nulls with “?”..."
Looking up what NULLIF( 109)='?' does I would expect that a '?' in column 109 is converted to NULL - and not the other way round (http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg31.htm)

I don't understand why you're mapping all these "Null_If..." fields. Is there any information in it you need?

Suspicious to me looks that DELV_ID has a length=6 in the attrib statement.
As a length value for a numeric variable in SAS is not the length of the string to be read but how many bites are used to store a number I assume this definition should be changed. Length should most likely be 8 for all numeric variables.

The HEX00 issue still remains.
The only ideas I have in the moment is to either use this "injection" approach and convert all HEX00 to ASCII blanks before mapping the vars - or to define the external file as user written and then use an input statement in the form
input @103 DELV_ID ?? s370ff6. ;

HTH
Patrick

[deleted_user]

Hi,

Thanx a ton for such great responses to the issues posted above.

I am sorry as I missed to replace the DELV_ID type as s370fpd6. instead of $ebcdic6.

I wanted to bring your notice that SAS generates a warning below when we use the informat value as s370fpdw. but it reads the data properly.

WARNING 150-185: List input (implied by INFORMAT) is incompatible with the specified binary informat. Formatted input will be used.

I tried to read DELV_ID as char and in the next process tried to convert into numeric but it isn't working. Would be great if you can tell me how it works so we can avoid customizing the file reader transformation code.

Actually our mainframe team uses the unloaded utilities to pull the data from DB2 table reside on Z/OS. And the file structure been generated by utilities automatically. Naturally we refer the file structure to define the source metadata but it isn't working as expected. Ultimately the unloaded data is in packed decimal format only.

However as Patrick said we have used the code @103 DELV_ID ?? s370fpd6. by selecting user written option to override the file reader transformation code. And it works fine.

When it comes to characteristic columns, I see the source data having HEX00 values. This is happening with columns defined as VARCHAR not with the columns defined as CHAR. Though there are no leading spaces in the DB2 tables for the columns defined as VARCHAR utilities fill the data with HEX00 for in the first 2 positions. It seems, something worng with utilities. I will check with our mainframe experts.

We have to use "Null If" variable to replace the nulls with blanks and dots. It is the practice we have been followed in our BI environment.

Thanks once again...
Warm Regards,
AnjiREDDY.

[sbb]

First, did you analyze all values of the field (in SAS, using PROC FREQ) as a SAS CHARACTER variable, so that you could learn about all incoming values?

Also, the latest "...not working.." comment requires sharing your code that is not working, ideally as part of a post-reply pasted from your log for the best forum subscriber response:

"I tried to read DELV_ID as char and in the next process tried to convert into numeric but it isn't working. Would be great if you can tell me how it works so we can avoid customizing the file reader transformation code."

And the use of "??" with an INFORMAT simply masks the WARNING message when you have incompatible data-field with the associated INFORMAT. So, if you are using an INFORMAT that expects to find a non-NULL data field, you should expect to receive the warning.

So, post your INPUT function code that is attempting to convert your CHARACTER to NUMERIC -- and share what you have learned about the various data-field strings you find in the DELV_ID when reading up as CHARACTER.


Scott Barry
SBBWorks, Inc.

[deleted_user]

Hi,

I haven't analyzed the incoming data using proc freq as I amn't familiar with this procedure.

I have defrined the DELV_ID as attrib DELV_ID length = 6 informat = $ebcdic6.; input @ 103 DELV_ID $ebcdic6.;

In the next transformation(Data Extract) I have used the expression input(DELV_ID,? 6.) aswell input(DELV_ID,? s370fpd6.). In botht the cases I see no data in the target table for this column.

Thanks and Regards,
AnjiReddy.

[sbb]

Suggest you double-check how you have declared your SAS variable - you are mixing a CHARACTER type variable and a NUMERIC type INFORMAT -- as discussed previously, this will not work (possibly contributing to your basic problem?) I expect you have SAS WARNING / NOTE messages in your log which need to be investigated and understood.

Also, you will want to take the opportunity to "learn about your incoming data", even if it means stopping what you're doing to analyze various fields in a CHARACTER format, before any attempt is made to convert the data.

So, is the field in PACKED_DECIMAL or DECIMAL format? Are you sure about how you are attempting to read the input file and the field in particular?

Again, I strongly encourage you to get a handle on knowing what's coming in -- no one else can help you there!

Scott Barry
SBBWorks, Inc.

[deleted_user]

I am sorry as I haven't copied the code that DI generated.

In DI studio I defined the column as char only so it generates the code as below

attrib DELV_ID length = $6 informat = $ebcdic6.;
input @ 103 DELV_ID $ebcdic6.;

I have verified the log file. I dunn see other warning or error messages apart from what I posted in prev post.

Yes I agree with you one should know what coming in, I would start knowing about proc freq and analyse the incoming data.

I tried to eliminate the junk chars using compress function but it doesn't allow me to use these chars directly, so I have found the ascii values of junk chars using substr and rank functions. And then tried to eliminate such chars using compress function. I am almost succeeded. Please have a look at the code below...

CCN = compress(CRED_CUST_NO,byte(0));
CCN_NEW = compress(CCN,byte(15));

Could you please tell me, how can we achieve the above in single expression?

Thanks and Regards,
AnjiREDDY.

[sbb]

Okay, so now we see that the DELV_ID is in fact declared as a CHARACTER variable, not NUMERIC (LENGTH=$6 as compared to LENGTH=6 -- very different behavior).

Look at using the TRANSLATE function for your character translation in a SAS assignment statement.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Hi,

using the expression below, one can eliminate all control characters (ascii values with in the range 0..31 considered as control characters)

CCN = compress(CRED_CUST_NO, ,'c')

Thanks and Regards,
AnjiREDDY.

[Patrick]

Hi

Defining a Hex 00 in SAS could look like '00'x -> compress(CRED_CUST_NO,'00'x);

You can of course use one of the modifiers to compress a whole group of HEX values.

I don't really understand why you're not already applying your conversion as an expression in the table mapping section of the file reader.

From a puristic point of view:
It's kind of disturbing that you can't map the variables already in it's correct format when reading from source.

I think I personally would pre-process the file in a pre-step (or have such a requirement for the ones delivering the file) and only then use the External file object to read and map the data.

Cheers
Patrick

[deleted_user]

Hi,

I am able to read tthe DELV_ID values without customizing the source code generated by file reader. I have defined the informat as $char6. instead of $ebcdic6. and then used the expression input(DELV_ID, s370fpd6.). It works fine.
I thought of using the compress function with modifier Compress(CRED_CUST_No, ,'c')in mapping itself. DI generates the proc SQL, and PRC SQL isn't allowing the modifier.
you may refer the log below...

2857 proc sql;
2858 create table work.W5TR1SOO as
2859 select BS_MANDATE_NO length = 6,
2860 CRED_CG_ID length = 6,
2861 (COMPRESS(CRED_CUST_NO, ,'c')) as CRED_CUST_NO length = 32 informat = $ebcdic32.,
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, LOWER, PUT, SUBSTRING, UPPER, USER.

Any work around to eliminate the control characters using PROC SQL? so I can stop customizing the file reader.
Thanks and Regards,
AnjiReddy.