topic Re: Trying to read Oracle Database with $ symbol in name using ODBC in SAS Programming

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

LeeRoy — Tue, 14 Nov 2017 10:45:38 GMT

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

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

RW9 — Tue, 14 Nov 2017 10:53:17 GMT

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).

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

LeeRoy — Tue, 14 Nov 2017 10:57:12 GMT

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

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

RW9 — Tue, 14 Nov 2017 11:31:17 GMT

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.

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

Ksharp — Tue, 14 Nov 2017 12:17:02 GMT

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;

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

LeeRoy — Tue, 14 Nov 2017 14:56:05 GMT

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