SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Covert Source table column from hex to decimal

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Covert Source table column from hex to decimal

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.


Accepted Solutions
Solution
‎04-14-2017 01:18 PM
Occasional Contributor
Posts: 9

Re: Covert Source table column from hex to decimal

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.


Hex to Decimal Table Loader Solution.png

View solution in original post


All Replies
Occasional Contributor
Posts: 9

Re: Covert Source table column from hex to decimal

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

PROC Star
Posts: 1,100

Re: Covert Source table column from hex to decimal

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?

Occasional Contributor
Posts: 9

Re: Covert Source table column from hex to decimal

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

PROC Star
Posts: 1,100

Re: Covert Source table column from hex to decimal

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;

 

Occasional Contributor
Posts: 9

Re: Covert Source table column from hex to decimal

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

PROC Star
Posts: 1,100

Re: Covert Source table column from hex to decimal

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

Super User
Posts: 5,260

Re: Covert Source table column from hex to decimal

In a mapping...?!
Data never sleeps
Solution
‎04-14-2017 01:18 PM
Occasional Contributor
Posts: 9

Re: Covert Source table column from hex to decimal

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.


Hex to Decimal Table Loader Solution.png
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 259 views
  • 0 likes
  • 3 in conversation