Trying to read Oracle Database with $ symbol in name using ODBC

[LeeRoy]

Hi guys,

Not sure if this is a relatively simple one but it is something I haven't come across before.  I have done some searching online and haven't come up with anything yet.

I am trying to read in some Oracle system performance data using ODBC and submitting the following code:

LIBNAME ORACLE ODBC DSN=ORC_DAT uid=user pwd=pwd SCHEMA=schema;

%let setorc = 'ORACLE.MGMT$METRIC_CURRENT';

data ORC_DATA;

set &setorc;

run;

I have managed to get the code working on a different table with no $ symbol.  The database guy has also tried to rename the table or create a view to no avail.  Finally I have also tried a lot of combinations of quotes as well.  The error I am getting for the code above is as follows:

ERROR: The physical file name "ORACLE.MGMT$METRIC_CURRENT" is too long

I guess my question is how can I read in the table and get SAS to ignore the $ symbol, as I assume this is the error.

Many Thanks

[RW9]

You could try literal naming:

%let setorc=ORACLE.MGMT$METRIC_CURRENT;
data orc_data;
  set "&setorc."n;
run;

Note, I would avoid using quotes in macro variables (and avoid coding in mixed case).

[LeeRoy]

Thanks for the quick reply.

Just tried this and am getting a different error:

ERROR: The value ORACLE.MGMT$METRIC_CURRENT is not a valid SAS name

[RW9 · Accepted solution]

And you are sure you are using exactly this code:

data orc_data;
  set oracle."MGMT$METRIC_CURRENT"n;
run;

Note the oracle is the libname, then a dot, then the name of the table in quotes with n after.

[Ksharp]

try 

option validmemname=extend;

libname oracle odbc ........

data have;

 set oracle.'MGMT$METRIC_CURRENT'n ;

run;

or use 

proc copy in=oracle out=work;

run;

[LeeRoy]

Massive thanks to both RW9 and Ksharp.

Using a combination of both literal naming and proc copy I have come to the realisation that SAS cannot see the datasets I need and this is most likely due to the way they have been created.  I am getting my database guy to dump the data into a flat file.

Many Thanks