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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.