Desktop productivity for business analysts and programmers

datatypes conversion

Reply
N/A
Posts: 1

datatypes conversion

I have a question regarding joining tables using different data types. I have one data type which is numeric length 8 and one char length 20.

I have tried converting the num8 to char20
PROC SQL;
CREATE TABLE HPXRDATAMINE AS
SELECT
LAB.MEME_CK AS MEME_CK,
HPXRDATA.MEM_UID AS MEM_UID
FROM &LIBHPXR..MEMBER_DIM as HPXRDATA
INNER JOIN WORK.LABCLAIMS AS LAB on HPXRDATA.MEM_SRCKEY=PUT(LAB.MEME_CK,20.);


Also tried converting char20 to num8



PROC SQL;
CREATE TABLE HPXRDATAMINE AS
SELECT
LAB.MEME_CK AS MEME_CK,
HPXRDATA.MEM_UID AS MEM_UID
FROM &LIBHPXR..MEMBER_DIM as HPXRDATA
INNER JOIN WORK.LABCLAIMS AS LAB on (NPUT(HPXRDATA.MEM_SRCKEY,8.)=LAB.MEME_CK;


Neither is bringing back anything (consistently). Any help would be great. I’ve tried doing the LEFT(TRIM()) function on the data as well.
Thanks.
Valued Guide
Posts: 2,111

Re: datatypes conversion

The numeric of length 8 is not 8 digits. It's 8 bytes to store a floating point number, so the INPUT function should use all 20 characters to get the entire number (your INPUT(...,8.) just takes the first 8 columns of the character variable and makes it a number.)

The PUT function left justifies the character string in the output. Your original character variable may be right justified. LEFT can be used to address that for the comparison.

The last consideration is the precision of the numeric representation. Your operating system guide will tell you the largest number that can be represented exactly as an integer in 8 bytes. If your char var is has 20 digits in it, you may have lost information in your numeric version. If you have lost precision, you are in deep trouble, an exact match can't be done.
Ask a Question
Discussion stats
  • 1 reply
  • 117 views
  • 0 likes
  • 2 in conversation