08-09-2017 02:41 PM - edited 08-09-2017 02:42 PM
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:
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!!
08-09-2017 03:11 PM
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:
08-09-2017 03:30 PM
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
data work.tablenameasyouwant ; /*or any sas libname you want to store data*/
08-10-2017 09:05 AM
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.
08-11-2017 10:58 AM
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....
08-11-2017 11:05 AM
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;
08-11-2017 11:25 AM
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.
08-11-2017 11:32 AM
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