Covert Source table column from hex to decimal

[gsmith]

I am using Data Integration studio (4.902). I have two source tables I am trying to join.

Table A has a column "patient_id" in a numeric field (SQLServer)

Table B has the corresponding column "Patient_id" but stored as a Hexadecimal value. (MySQL)

How do I convert the Hex value in table B to allow me to join it to the decimal value from table A?

The solution I came up with, and am struggling with, is to use the Table Loader transformation to convert the column

in table B then join the resulting Work Table to table A.

I have not been able to successfully convert the Hexadecimal value when loading it into the work table.

I have tried using INPUT(B.patient_id, $hex11.) as an expression, with infomat $11. and format $11. I do not get a valid value.

For example B.patient_id = 4D32 is coming out as A.patient_id = M2

There has to be a more direct way of doing this.

[gsmith]

I miss typed the line "For example B.patient_id = 4D32 is coming out as A.patient_id = M2"

It should read "For example B.patient_id = 4D32 is coming out as WorkTable.patient_id = M2

[TomKari]

First question: if you just convert your MySQL Table B to a SAS work dataset, what are the characteristics (numeric or character, length) of the resulting column in the SAS dataset?

[gsmith]

When I map table B directly into a SAS data set I get 

Type = Character Size = 5, Informat = $5. and format = $5.   and the data value is the same as the original Hex value in table A

[TomKari]

Hex is a nuisance, as you're finding out!

Here's what I think you want, as an example. It converts character hex values to SAS numbers correctly. Note that the "strip" is very important to pass ONLY the hex characters to the input.

data Want;

length patient_id $5;

input patient_id;

num_patient_id = input(strip(patient_id), hex.);

cards;

A4D32

4D32

D32

32

3

run;

[gsmith]

That certainly works, how would I use that in a Data Integration Job?

[TomKari]

You mention using the Table Loader transformation to convert the column.

Although I don't have DI Studio available, I believe that if you set up your target column as numeric, and transform your source column with a function of input(strip(patient_id), hex.), the resulting column will be able to be used to join with your other table.

Tom

[LinusH]

In a mapping...?!

[gsmith · Accepted solution]

It worked. I forgot to add the "input" at the begining of the code line.

 input(strip(treatment_plans.patient_id), hex.) worked when I changed the type to numeric in the target column.

I've attached a paint document to the solution so there is a visual record of the solution as well.

Thanks for the assist folks. It's appreciated.