topic Re: Problem converting HEX format to character in SAS Programming

Problem converting HEX format to character

Wolverine — Thu, 07 Mar 2019 13:42:35 GMT

I have a collection of data that with IDs that have been encrypted in a hex format. Sometimes, as part of testing, I find it helpful to follow just a few cases thru the syntax to make the correct cases are being selected at each step. To do so, I use the following syntax in a data step:

encrypted_id2 = put(encrypted_id, $hex64.);
IF encrypted_ID2 ^in ("0720275C8B0B0C376C1B272DA78FD0DF10FB5408EDE76CD21C637EF2F42FC4AB") then delete;

I've used this bit of syntax dozens of times while working with this data. But on this particular occasion, I got an error that I am at a loss to explain:

ERROR 29-185: Width specified for format HEX is invalid.

I've confirmed that the ID is in fact 64 characters. I tried searching online but couldn't find any useful info. So what could possibly cause this issue?

Re: Problem converting HEX format to character

Tom — Thu, 07 Mar 2019 13:47:12 GMT

Most likely your id variable is numeric instead of character.

9     data _null_;
10      length x 8;
11
12      y =put(x,$hex64.);
                 -------
                 29
WARNING: Variable x has already been defined as numeric.
ERROR 29-185: Width specified for format HEX is invalid.

13    run;

Re: Problem converting HEX format to character

Kurt_Bremser — Thu, 07 Mar 2019 13:56:45 GMT

Maxim 3: Know Your Data.

encrypted_id is numeric, so SAS (trying to be helpful) changes the character format $HEX to the numeric format HEX (note the missing dollar sign in the "ERROR 29-185: Width specified for format HEX is invalid."). But since the numeric format expects a maximum of 8 bytes as input (the maximum number of bytes for SAS numbers), it only allows up to 16 as width (Maxim 1: Read the Documentation).

For your put() function call to make sense, enrypted_id needs to be character with a length of 32.

Re: Problem converting HEX format to character

Wolverine — Thu, 07 Mar 2019 16:26:27 GMT

You're right -- I got the same warning just above the error: WARNING: Variable encrypted_id has already been defined as numeric.

That means that this particular data file was created differently than the rest of the files in this data collection. I ran proc contents on this file, and on another file from this collection.

Current file: encrypted_id Num 8

Other file: encrypted_id Char 200 $HEX64.

I don't understand how encrypted_id can be 8-digit numeric when it includes letters and has an actual length of 64. The long string of numbers and letters in my first bit of syntax was copied directly from the SAS data file and is indeed 64 characters long. And how do I delete any record that doesn't belong to a specific ID?

Re: Problem converting HEX format to character

Kurt_Bremser — Mon, 11 Mar 2019 06:35:06 GMT

@Wolverine wrote:

You're right -- I got the same warning just above the error: WARNING: Variable encrypted_id has already been defined as numeric.

That means that this particular data file was created differently than the rest of the files in this data collection. I ran proc contents on this file, and on another file from this collection.

Current file: encrypted_id Num 8

Other file: encrypted_id Char 200 $HEX64.

I don't understand how encrypted_id can be 8-digit numeric when it includes letters and has an actual length of 64. The long string of numbers and letters in my first bit of syntax was copied directly from the SAS data file and is indeed 64 characters long. And how do I delete any record that doesn't belong to a specific ID?

You need to go back in your process and look how the dataset was created.

If you have proc import and/or the processing of Excel files somewhere in your process, you need to replace that with a reliable method. Importing Excel files is not reliable and therefore useless for repeated processing.

Re: Problem converting HEX format to character

Wolverine — Mon, 11 Mar 2019 12:15:24 GMT

Unfortunately I didn't create the data sets, and the guy who did no longer works here. However, he left his code as documentation. The data were pulled from a restricted-access data warehouse, not from Excel. Here is the section where the encrypted_ID is created for the PREVIOUS data sets:

	/*  1.4 Encrypt beneficiary_id */
	proc sql;

	    create table work.scd_data_3 as

	    select  sha256(put(input(beneficiary_id, 10.), z10.))         as encrypted_id format $hex64.
				,category
				,category_description
	    from    work.scd_data_2;

	quit;

And this is how he created it for the CURRENT data set:

/* 5.2 Create an encrypted ID */
proc sql;

create table work.encrypted_ids_2 as

select beneficiary_id
,sha256(beneficiary_id) as encrypted_id format $hex64.
from work.encrypted_ids_1;

quit;

Re: Problem converting HEX format to character

Kurt_Bremser — Mon, 11 Mar 2019 12:51:11 GMT

Both uses of the sha256 function create a character variable:

data have;
input beneficiary_id :$10.;
cards;
0123456789
;
run;

proc sql;
create table want1 as
select
  beneficiary_id,
  sha256(put(input(beneficiary_id, 10.), z10.)) as encrypted_id1 format $hex64.,
  sha256(beneficiary_id) as encrypted_id2 format $hex64.
from have;
quit;

It's just that the second version allows non-numeric data in the input, but the input (beneficiary_id) has to be character for both versions to work.

Re: Problem converting HEX format to character

Wolverine — Mon, 11 Mar 2019 13:48:40 GMT

The data is restricted, and beneficiary_id can be linked to patient names. So beneficiary_id is converted to encrypted_ID, and then beneficiary_id is dropped from the dataset before I'm allowed to have access to it. So, going back to the original question, how do I convert encrypted_id so I can I create a test dataset that only includes a few specific encrypted_id's?

Re: Problem converting HEX format to character

Kurt_Bremser — Mon, 11 Mar 2019 13:56:25 GMT

What I wanted to point at:

BOTH codes create a character variable. Since your initial problem seems to be that you have a dataset where encrypted_id is numeric, this dataset CANNOT come from one of the codes you posted.

It may even be that encrypted_id does not exist in this dataset, and is created by SAS on-the-fly, which would make it numeric (per default). In that case you would also most likely get an "uninitialized" NOTE.

Please post the log of the whole step that results in the message

ERROR 29-185: Width specified for format HEX is invalid.