topic Re: having problem with missing values for numerical columns in SAS Procedures

having problem with missing values for numerical columns

deleted_user — Tue, 09 Mar 2010 09:46:51 GMT

Dear All,

Our maiframe team have been used unload utiilities to pull the data from DB2 tables exist in Z/OS environment.

With in SAS we have used the FTP access method to access the data from external files reside on Z/OS.

We have problem with reading data from numerical columns which allow null values.

We had a chance to look at the hosted file reference structure to define the metadata in SAS. The structure looks as below...

000001 LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
000002 SCHEMA.TABLE_NAME
000003 (
000004 COLUMN1 POSITION( 1: 6)
000005 DECIMAL ,
000006 COLUMN2 POSITION( 7 )
000007 VARCHAR
000008 NULLIF( 27)='?',
000009 COLUMN3 POSITION( 28: 33)
000010 DECIMAL
000011 NULLIF( 34)='?',
000012 COLUMN4 POSITION( 35 )
000013 CHAR( 4)
000014 NULLIF( 39)='?',
000015 COLUMN5 POSITION( 40 )
000016 VARCHAR
000017 NULLIF( 53)='?'
000018 )

We define the metadata in SAS to access the above data as below...

Name Length Type Informat Begin POsition End Position
Column1 8 NUmeric s370fpd6. 1 6
Column2 20 Character $ebcdic20. 7 26
Null_If_1 1 Character $1. 27 27
Column3 8 NUmeric s370fpd6. 28 33
Null_If_2 1 Character $1. 34 34
Column4 5 Character $ebcdic5. 35 39
Null_If_3 1 Character $1. 40 40
Column5 14 Character $ebcdic14. 43 52
Null_If_4 1 Character $1. 53 53

We are able to pull the data from external file with the metadata defined as above except for the column3

I amn't sure if we define the metadata for the column3 preoperly...

And strangely we have to read the data for the column5 from the position 43 not from 41. If we read data from the position 41 a special character prefixed with these column values so we skipped the position 41 and reading data from the position 42 to avoid that special character

Could you please explain why should we read data from position 42 to skip that character, if you have expertise on this?

Thanks in advance, I can imagine you would have spent quite a long time to analyse the query I am posting here...

I am excited to see your reply.

Thanks once agian for your time.

Best Regards,
AnjiREDDY

Re: having problem with missing values for numerical columns

sbb — Tue, 09 Mar 2010 12:31:22 GMT

I don't believe that your DB2-unloaded DECIMAL specification matches the use of SAS INFORMAT s370fpd6.

Also, I am uncertain about your other concern, unless you can share a few data-row samples (pasted in a reply) and also some SAS processing log results are preferred for additional diagnosis and feedback.

Scott Barry
SBBWorks, Inc.

Re: having problem with missing values for numerical columns

deleted_user — Tue, 09 Mar 2010 13:18:46 GMT

Hi,

Thanx a ton for responding to my issue...

I have manipulated above file structure and the data provided here for security reasons
The unloaded data is stored in packed decimal format. You can refer the sample data provided below...

000001 % 00000000000test { * %
000002 Ø 080790000100996 { {
000003 ð 080790000100988 Ø { {
000004 { XXJ000000000008 ð { %

when I say hex on in Z/OS environment the above data looks as below

000001 % 00000000000test { * %
00001600021200FFFFFFFFFFFA8AA0000000000000000000290000010050000060000000
00005C00001C0F00000000000352300000000000000001008C00000C31C00016C0000000
------------------------------------------------------------------------------
000002 Ø 080790000100996 { {
00001700020300FFFFFFFFFFFFFFF0000000000000000000390000010090000110000000
00005C00001C0F08079000010099600000000000000001001C00000C87C00101C0000000
------------------------------------------------------------------------------
000003 ð 080790000100988 Ø { {
00001800020300FFFFFFFFFFFFFFF0000000000000000000370000010090000090000000
00005C00001C0F08079000010098800000000000000001002C00000C87C00107C0000000
------------------------------------------------------------------------------
000004 { XXJ000000000008 ð { %
00001900020100EEDFFFFFFFFFFFF0000000000000000110380000010090000160000000
00005C00001C0F77100000000000800000000000000000003C00000C87C00108C0000000

We don't access DB2 tables directly. Our team uses load utilities to pull data from DB2 tables and then SAS job reads the unloaded data(in packed decimal format) to populate EDW tables.

We have problem with only numerical columns which allow null values.
We are able to access data from non numerical columns which allow null values

When we use the informat as pd5. it allows to access the data but it skips the last digit where as when we use pd6., it provides the incorrect values..

However, here I am writing the few of SAS log lines...

14 attrib DBTR_IBAN_NO length = $36 informat = $ebcdic36.;
15 attrib NULL_IF_IBAN length = $1 informat = $ebcdic1.;
16 attrib DELV_ID length = 8 informat = s370fpd6.;
_________
150
WARNING 150-185: List input (implied by INFORMAT) is incompatible with the specified binary informat. Formatted input will be used.

NOTE: Invalid data for DELV_ID in line 1 103-108.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

1 CHAR .....l... .,..ððððððððððð£.¢£...................(.........\.....l...................................
ZONE 00001600021200FFFFFFFFFFFA8AA00000000000000000002900000100500000600000000000000000000000000000000000
NUMR 00005C00001C0F00000000000352300000000000000001008C00000C31C00016C00000000000000000000000000000000000

101 .o......oÃÙÅÄ...................oðñKðôKòððùðñKðôKòððùóñKñòKùùùù..Š.¢.....kÂ×mðòõkâÃÅ................
ZONE 060000006CDCC00000000000000000006FF4FF4FFFFFF4FF4FFFFFF4FF4FFFF01A9A889896CD6FFF6ECC0000000000000000
NUMR 0F000000F39540000000000000000000F01B04B200901B04B200931B12B99990367214495B27D025B2350000000000000000

201 .....òððù`ðó`ñð`ñõKõöKòõKöøôððð
ZONE 00000FFFF6FF6FF6FF4FF4FF4FFFFFF
NUMR 000002009003010015B56B25B684000
DELV_ID=. NULL_IF_DELV=?_ERROR_=1 _N_=1

Re: having problem with missing values for numerical columns

sbb — Tue, 09 Mar 2010 13:49:55 GMT

We need to see your DATA step program completely with the INPUT statement(s) at a minimum, as well as your INFILE statement too. Plz explain for us where you are processing this data with SAS and if the processing is not natively happening on z/OS, tell us exactly how you get the data to the OS platform where it is processed.

Read the post below for guidelines with handling code, special characters, when constructing your SAS discussion forums post.

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.

Re: having problem with missing values for numerical columns

deleted_user — Wed, 10 Mar 2010 06:46:25 GMT

Thanx for your promptive response.

We process the job in windows environment only. We have been using ftp access method to access the dataet located in Z/OS.

Please have a look at the data step program below...

[pre]
1 data work.etls_EFITable;
2
3 infile "'A1.Z1D.ML1D6.MUNL02.D100219'" Ftp Host='HOST' Port=21
4 firstobs = 1
5 Binary lrecl=231 blocksize=32000 debug USER='user' PASS=XXXXX;
6
7 attrib BS_MANDATE_NO length = 8 informat = s370fpd6.;
8 attrib CRED_CG_ID length = 8 informat = s370fpd6.;
9 attrib CRED_CUST_NO length = $32 informat = $ebcdic32.;
10 attrib DBTR_NO length = 8 informat = s370fpd6.;
11 attrib DBTR_ACCT_CATEGORY_ID length = 8 informat = s370fpd6.;
12 attrib DBTR_REG_NO length = 8 informat = s370fpd3.;
13 attrib DBTR_ACCT_NO length = 8 informat = s370fpd6.;
14 attrib DBTR_IBAN_NO length = $36 informat = $ebcdic36.;
15 attrib NULL_IF_IBAN length = $1 informat = $ebcdic1.;
16 attrib DELV_ID length = 8 informat = s370fpd6.;
17 attrib NULL_IF_DELV length = $1 informat = $ebcdic1.;
18 attrib CREATED_BY length = $4 informat = $ebcdic4.;
19 attrib NUL_IF_2 length = $1 informat = $ebcdic1.;
20 attrib REPORTING_IDENT length = $16 informat = $ebcdic18.;
21 attrib NULL_IF_ID length = $1 informat = $ebcdic1.;
22 attrib START_DT length = $10 informat = $ebcdic10.;
23 attrib VAL_START_DT length = $10 informat = $ebcdic10.;
24 attrib VAL_END_DT length = $10 informat = $ebcdic10.;
25 attrib UPDATE_BY length = $42 informat = $ebcdic42.;
26 attrib UPDATE_TS length = $26 informat = $ebcdic26.;
27
28 input @ 1 BS_MANDATE_NO s370fpd6.
29 @ 7 CRED_CG_ID s370fpd6.
30 @ 13 CRED_CUST_NO $ebcdic32.
31 @ 45 DBTR_NO s370fpd6.
32 @ 51 DBTR_ACCT_CATEGORY_ID s370fpd6.
33 @ 57 DBTR_REG_NO s370fpd3.
34 @ 60 DBTR_ACCT_NO s370fpd6.
35 @ 66 DBTR_IBAN_NO $ebcdic36.
36 @ 102 NULL_IF_IBAN $ebcdic1.
37 @ 103 DELV_ID s370fpd6.
38 @ 109 NULL_IF_DELV $ebcdic1.
39 @ 110 CREATED_BY $ebcdic4.
40 @ 114 NUL_IF_2 $ebcdic1.
41 @ 117 REPORTING_IDENT $ebcdic18.
42 @ 133 NULL_IF_ID $ebcdic1.
43 @ 134 START_DT $ebcdic10.
44 @ 144 VAL_START_DT $ebcdic10.
45 @ 154 VAL_END_DT $ebcdic10.
46 @ 164 UPDATE_BY $ebcdic42.
47 @ 206 UPDATE_TS $ebcdic26.;
48
49 run;
[/pre]

We have problem with the DELV_ID field which is defined as numeric and allow null values at DB2 end.

When we use the informat type as s370fpd6., it says invalid data, when we use pd5. it reads data but misses to show the last digit of the DELV_ID values.

Kindly revert, if you have difficulties in analysing the query that I posted here.

Thanks and Regards,
AnjiREDDY

Re: having problem with missing values for numerical columns

sbb — Wed, 10 Mar 2010 12:05:14 GMT

If you permit some type of NULL-VALUE field in your input data which is not correctly formatted for the specified SAS INFORMAT, you will need to code ?? prior to naming the INFORMAT. Have a look at the DOC for details on its purpose.

Scott Barry
SBBWorks, Inc.

Re: having problem with missing values for numerical columns

deleted_user — Wed, 10 Mar 2010 13:38:57 GMT

We use SAS DI studio to process the data. Naturally we have used file reader transformation to read the data from external files.

Could you please explain, how we can use "??" to handle null values in file reader?

Thanks and Regards,
AnjiREDDY.

Re: having problem with missing values for numerical columns

sbb — Wed, 10 Mar 2010 14:42:31 GMT

I am unfamiliar with answering your DI-specific question about how/where to introduce the "??" parameter for "file reader" - possibly. Are you getting a SAS ERROR or a NOTE/WARNING message when you run your program with INFORMAT s370fpd6. specified?

Check the SAS Language Elements discussion on the INPUT statement and using INFORMATs - particularly, the topic on INVALID DATA. The only relief it provides is to mask the condition in the SAS log, suppressing the warning message. Consider though (I believe) you still end up with the desired result, a SAS numeric variable with a missing value condition.

Possibly there may be information here (or you may need to contact SAS tech support for guidance) - or just ignore the SAS log warning messages about INVALID DATA or INVALID ARGUMENT TO FUNCTION:

http://support.sas.com/documentation/cdl/en/etlug/60948/HTML/default/p0rhnqjrb6tje9n1jyfk9h4kn386.htm

Scott Barry
SBBWorks, Inc.

Google advanced search argument, this topic/post:
input statement invalid numeric data site:sas.com