BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gsmith
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
gsmith
Obsidian | Level 7

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

8 REPLIES 8
gsmith
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

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

TomKari
Onyx | Level 15

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
Tourmaline | Level 20
In a mapping...?!
Data never sleeps
gsmith
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 2602 views
  • 1 like
  • 3 in conversation