topic Re: Covert Source table column from hex to decimal in SAS Data Management

Covert Source table column from hex to decimal

gsmith — Fri, 14 Apr 2017 00:27:48 GMT

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.

Re: Covert Source table column from hex to decimal

gsmith — Fri, 14 Apr 2017 00:42:31 GMT

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

Re: Covert Source table column from hex to decimal

TomKari — Fri, 14 Apr 2017 01:09:24 GMT

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?

Re: Covert Source table column from hex to decimal

gsmith — Fri, 14 Apr 2017 01:32:47 GMT

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

Re: Covert Source table column from hex to decimal

TomKari — Fri, 14 Apr 2017 02:07:56 GMT

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

run;

Re: Covert Source table column from hex to decimal

gsmith — Fri, 14 Apr 2017 03:15:12 GMT

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

Re: Covert Source table column from hex to decimal

LinusH — Fri, 14 Apr 2017 09:34:00 GMT

In a mapping...?!

Re: Covert Source table column from hex to decimal

TomKari — Fri, 14 Apr 2017 14:19:08 GMT

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

Re: Covert Source table column from hex to decimal

gsmith — Fri, 14 Apr 2017 17:16:34 GMT

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.