Is it possible to read RAW data type in SAS?

Question

I am working with SAS and I am using data from an Oracle database via an ODBC connection. There are some fields I require from this database that have data_type = RAW in the Oracle SQL Developer environment.

SAS is reading these in incorrectly and is returning every field as 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A with Type = Character and Format and Informat = $HEX40.

One thing I tried to do is read it in as a character variable instead, using character formats and informats using the following code, where mylib is the library connected to an Oracle database.

data want;
set mylib.have (obs= 10000);
format raw_data_var char40.;
informat raw_data_var char40.;
run;

This changed the formats to character but it then converted the cells to ********************

I also tried find some SAS documentation on reading binary data, https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p1vu9u7w1ieua7n17973igt2cq3c.htm&docsetVersion=9.4&locale=en but unfortunately, I could not find something useful to help.

Can someone point me in the right direction to read in a raw data type using a data step or proc sql?

Thank you

What did you expect the first 20 bytes of your RAW object to contain if not '2A'x repeated 20 times? Why not just ask Oracle to convert it to a hex string so SAS just sees it as a character variable? — Tom
The first question was not rhetorical. How do you know what SAS showed was wrong if you don't know what the right answer is? You don't HAVE to modify the Oracle database to ask Oracle to convert the values on its side. See the answer from @Richard. — Tom

Richard Richard · Accepted Answer · 2020-02-06T20:35:03

You could use Proc SQL with a pass though query that utilizes the Oracle function RAWTOHEX

proc sql;
  connect using mylib;
  create table want as 
  select 
    a,b,c,input(rawhexed,$HEX32000.) as raw16kchars 
  from
    connection to mylib
    (
        select a,b,c,rawtohex(myraw) as rawhexed
        from have /* oracle side reference */
    )
  ;


quit;

Is it possible to read RAW data type in SAS?

1 Answers