topic Re: Connecting SAS EG to Oracle in SAS Programming

Connecting SAS EG to Oracle

Amber_Nicole94 — Wed, 09 Aug 2017 18:42:12 GMT

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!!

Re: Connecting SAS EG to Oracle

Reeza — Wed, 09 Aug 2017 19:11:20 GMT

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:

https://support.sas.com/techsup/technote/ts765.pdf

Re: Connecting SAS EG to Oracle

Amber_Nicole94 — Wed, 09 Aug 2017 19:24:45 GMT

Yes, it is installed on a server. Using your resource I was stopped at the "administrative tools" step because i dont have those privledges.

Re: Connecting SAS EG to Oracle

kiranv_ — Wed, 09 Aug 2017 19:30:57 GMT

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;

Re: Connecting SAS EG to Oracle

Reeza — Wed, 09 Aug 2017 19:40:09 GMT

@Amber_Nicole94 Someone who has admin privileges on the server will need to set it up then.

Re: Connecting SAS EG to Oracle

Amber_Nicole94 — Thu, 10 Aug 2017 13:05:41 GMT

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.

Re: Connecting SAS EG to Oracle

Reeza — Thu, 10 Aug 2017 17:45:48 GMT

Run a proc datasets on the library and double check the data sets names that you need.

proc datasets lib=cvs;
run;quit;

Re: Connecting SAS EG to Oracle

Amber_Nicole94 — Fri, 11 Aug 2017 14:58:22 GMT

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

Re: Connecting SAS EG to Oracle

Reeza — Fri, 11 Aug 2017 15:05:29 GMT

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;

Re: Connecting SAS EG to Oracle

Reeza — Fri, 11 Aug 2017 15:07:28 GMT

Is it edMp or edWp, M or W? Your code uses EDMP and your later post references EDWP.

Re: Connecting SAS EG to Oracle

Amber_Nicole94 — Fri, 11 Aug 2017 15:25:13 GMT

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.

Re: Connecting SAS EG to Oracle

Reeza — Fri, 11 Aug 2017 15:32:22 GMT

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 🙂