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
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.
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).
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
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.