Help is needed for converting DB2 Decimal to SAS string

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Help is needed for converting DB2 Decimal to SAS string

Hi,

I am trying to read few columns from DB2 table and one of the column type is decimal. For some reason it changes the format (last few digit/digits of the number) when I create a SAS dataset. Tried many different options but nothing seems to be working. I need to convert that 19 digit long number into string and store in SAS dataset.

DB2 Table name: mytable

Field name: T_ID

Field type: Decimal

Field length: 19

For example

Original DB2 id                   0212127697604774814

Current SAS dataset output:    212127697604775000

Desired SAS dataset output:   0212127697604774814

proc sql;                                   

CREATE TABLE WORK.temp AS

(

           Select                     

           T_Id,

           T_name,

           T_age

           FROM mydb2lib.mytable

          

);

Run;

Any help would be appreciated.

Thanks.


Accepted Solutions
Solution
‎03-28-2014 09:18 AM
Super User
Posts: 5,256

Re: Help is needed for converting DB2 Decimal to SAS string

There are many recent threads in forum on this topic, with long numerical fields in RDBMS.

You probably need to convert it at the source, that is doing your conversion using explicit SQL pas-thru.

Data never sleeps

View solution in original post


All Replies
Solution
‎03-28-2014 09:18 AM
Super User
Posts: 5,256

Re: Help is needed for converting DB2 Decimal to SAS string

There are many recent threads in forum on this topic, with long numerical fields in RDBMS.

You probably need to convert it at the source, that is doing your conversion using explicit SQL pas-thru.

Data never sleeps
Super Contributor
Posts: 644

Re: Help is needed for converting DB2 Decimal to SAS string

SAS can allocate a maximum of 8 bytes storage for a numerical value and this only allows 15-16 digits precision (slightly more on zOS).  You need to read the ID as a character variable into SAS, where the full string including the leading zero can be preserved.  You may be able to accomplish this with SQL passthrough using a DB2 function to convert the decimal to a text value.  Alternately you can ask your DB2 admin to create a view of the table which transforms ID to a text field, and select  from the view.

You will have the same problem if you need to export your data to Excel, where any numbers beyond the 15th will be trimmed to zero.  Again the answer is to import as a character value.  There has been recent discussion of this on this forum.

Richard

New Contributor
Posts: 2

Re: Help is needed for converting DB2 Decimal to SAS string

Thanks Linus and Richard.

I tried pass through and output is closer to the expected results.

Test1 : 212127697604774816 (incorrect number)

Test2 : 212127697604774814 ( 0 is missing from the string)

Desired SAS dataset output:   0212127697604774814

Select                     

           T_Id as test1,

Trim(T_Id) as test2,

           T_name,

           T_age

           FROM mytable

Thanks,

KP

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 509 views
  • 3 likes
  • 3 in conversation