I have access to SAS EG here at work. Currently, I have data download from a third party database (BOBJ) into multiple excel sheets which I then inport into SAS. However, in order to bypass the excel step I am attempting to connect straight to the oracle database in SAS.
I am brand new to oracle so am stuck when in comes to coding the connection. The following is some of the information I have.
libname = CVS
oracle user = my user name
password = my password
path = edmp
schema = cvs_own
Oracle tables are called:
IDWE_D_CALENDAR
IDWE_OWN.BUSINESS_TYPE
IDWE_OWN.ACCOUNT_NAME
ETC....
Hoping smeone can assist in writing the code to bring in data with the same affect as importing an excel sheet. As specific as you can be would be appreciated as I am fairly new to the oracle piece!!
Is EG installed locally? Or on a server? If it's a server, your IT area will need to do this for you.
If it's locally, first set up the server in your computers ODBC setup, via the control panel.
Then use something like the following:
libname myDB dsn=myName schema=dbo;
You can see a variation of these instructions in detail here:
Yes, it is installed on a server. Using your resource I was stopped at the "administrative tools" step because i dont have those privledges.
@Amber_Nicole94 Someone who has admin privileges on the server will need to set it up then.
As suggested by @Reeza you need to first do first define libname for Oracle database. For example
libname mydblib oracle user=testuser password=testpass path=hrdept_002;
example was copied from
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113591.htm
and then
data work.tablenameasyouwant ; /*or any sas libname you want to store data*/
set mydblib.tablenameofyourinterest;
run;
libname CVS oracle user=XXXX password=XXXX PATH='edmp' schema='cvs_own';
DATA NEW;
SET CVS.IDWE_D_CALENDAR;
RUN;
PROC SQL;
CREATE TABLE NEW AS SELECT *FROM CVS.D_CALENDAR;
QUIT;
Above is the code I have so far. Seems to work up until the SET step. I recieve the error
"File CVS.IDWE_D_CALENDAR.DATA does not exist."
I just stuck the table name in refrencing the example given above but not sure I referenced it accurately.
Run a proc datasets on the library and double check the data sets names that you need.
proc datasets lib=cvs;
run;quit;
When I enter the suggested code above it states I do not have any datasets. I contacted my IT and I was told it should be good to go, and the "ODBC data source is just the name of the database you want to use as a source for your data – so you have to identify that yourself first – could be EDWP or SAST or any other." So still unsure of why it is not working....
Redo the libname and remove the quotes around the values, I'm pretty sure it doesn't need them. I'm not certain about the path vs dsn specification so I'd try that as well.
libname CVS oracle dsn = edmp user=XXXX password=XXXX schema=cvs_own;
Is it edMp or edWp, M or W? Your code uses EDMP and your later post references EDWP.
It is EDMP, the W was an error from my tech support.
I was finally directed to someone who said I do need access to the Oracel Client to set up ODCB. So I have put that request in. Once it is approved I will come back and try the suggetions above once more.
If your server is connected you shouldn't...only if you're working locally.
But there may be something about your set up that we don't know, so I'd follow your companies advice for now 🙂
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.