BookmarkSubscribeRSS Feed
Amber_Nicole94
Obsidian | Level 7

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

 

 

 

 

11 REPLIES 11
Reeza
Super User

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

Amber_Nicole94
Obsidian | Level 7

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

Reeza
Super User

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

kiranv_
Rhodochrosite | Level 12

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;

Amber_Nicole94
Obsidian | Level 7
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.

 

Reeza
Super User

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

 

proc datasets lib=cvs;
run;quit;
Amber_Nicole94
Obsidian | Level 7

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

 

Reeza
Super User

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;
Reeza
Super User

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

 

Amber_Nicole94
Obsidian | Level 7

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.

Reeza
Super User

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 🙂

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 6949 views
  • 2 likes
  • 3 in conversation