DATA Step, Macro, Functions and more

Connecting SAS EG to Oracle

Reply
Contributor
Posts: 25

Connecting SAS EG to Oracle

[ Edited ]

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

 

 

 

 

Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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

Contributor
Posts: 25

Re: Connecting SAS EG to Oracle

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

Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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

PROC Star
Posts: 326

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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;

Contributor
Posts: 25

Re: Connecting SAS EG to Oracle

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.

 

Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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

 

proc datasets lib=cvs;
run;quit;
Contributor
Posts: 25

Re: Connecting SAS EG to Oracle

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

 

Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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;
Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

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

 

Contributor
Posts: 25

Re: Connecting SAS EG to Oracle

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.

Super User
Posts: 19,869

Re: Connecting SAS EG to Oracle

Posted in reply to Amber_Nicole94

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 Smiley Happy

Ask a Question
Discussion stats
  • 11 replies
  • 215 views
  • 2 likes
  • 3 in conversation