DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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

 

 


Accepted Solutions
Solution
‎11-14-2017 09:56 AM
Super User
Super User
Posts: 9,209

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

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.

View solution in original post


All Replies
Super User
Super User
Posts: 9,209

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

[ Edited ]

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

New Contributor
Posts: 3

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

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

 

Solution
‎11-14-2017 09:56 AM
Super User
Super User
Posts: 9,209

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

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.

Super User
Posts: 10,611

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

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;

New Contributor
Posts: 3

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 159 views
  • 1 like
  • 3 in conversation