SAS Studio - link to Oracle Database

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

SAS Studio - link to Oracle Database

I would like to ask how do I link my Oracle Database to SAS Studio?

SAS(R) 9.2 Intelligence Platform: Data Administration Guide

I see from above link that we need to setup the connectivity, but it does not seem to apply in SAS Studio.

Please help. Thank you.


Accepted Solutions
Solution
‎02-09-2015 09:47 PM
SAS Super FREQ
Posts: 8,820

Re: SAS Studio - link to Oracle Database

Hi:

  You are correct that without the SAS/Access modules you cannot connect to your Oracle database directly. SAS University Edition and SAS Studio are intended for learning purposes for many of our basic programming and statistical analysis classes. We do not cover SAS/ACCESS to Oracle in our programming classes, however, you can read delimited files, such as tab-delimited files, comma-delimited files and ASCII text files using SAS Studio and you can use PROC IMPORT to read Excel files.  If you have a full installation of SAS with the correct "helper" modules, you would be able to connect to an Oracle table, just as you do with R. But your syntax would be different, as shown here in the document with the LIBNAME statement for Oracle:

libname mydblib oracle user=testuser password=testpass path=hrdept_002;

  

proc print data=mydblib.employees;

   where dept='CSR010';  

run;

as shown in this documentation example:  SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

  But this will not work in SAS Studio, since you do not have the SAS/ACCESS to Oracle "helper" module. So you will have to look to other techniques to read your data, such as exporting from Oracle first (possibly as a delimited file) and then importing that file into SAS. In addition, the document you referenced on Data Administration for the SAS Platform is meant to be used when you are defining metadata and setting up libraries for the SAS Platform for Business Analytics (also known as the BI Platform), so without the rest of the Platform infrastructure, this is probably not the manual you should use for reference with SAS Studio. We cover explicit examples of using SAS Studio in our free Programming 1 and Statistics 1 e-learning classes. And, each course comes with a setup section that has a program where you can make data that can be used for analysis.

  To find the links to the free training, just go here: SAS Tutorials | SAS Training and look at the 2 links on the lower right-hand corner of the "New to SAS" page.

cynthia

View solution in original post


All Replies
Super User
Posts: 3,235

Re: SAS Studio - link to Oracle Database

SAS Studio is simply a web interface for running SAS code. If that code is being run on a SAS server which has Oracle connectivity set up, then it should just run, just as if it were code run via EG, or batch jobs etc.

Occasional Contributor
Posts: 10

Re: SAS Studio - link to Oracle Database

thanks SASKiwi for your kind reply.

I am a new SAS user, so need some kind of your guidance.

I am more of a seasoned R user, and I can link R to my Oracle easily with below code.

library(RODBC)

Oracle_conn1<- odbcConnect("XYZ", uid = "ABCDE", pwd = "XXXXX")

odbcGetInfo(Oracle_conn1)

# Find out what tables are available

Tables1 <- sqlTables(Oracle_conn1)

TEST1 <- sqlQuery(Oracle_conn1, "SELECT ITNO, TIME_TEST FROM XYZ.EXAMPLE WHERE ITNO = '1345677'")

How can I do it in SAS, such that I am able to bring in the required table and fields inside SAS, and write the SQL commands to extract some data?

Super User
Posts: 3,235

Re: SAS Studio - link to Oracle Database

You need SAS/ACCESS to Oracle or SAS/ACCESS to ODBC installed and licensed to provide the Oracle connectivity. You can use proc setinit to confirm what SAS products you are licensed for.

Occasional Contributor
Posts: 10

Re: SAS Studio - link to Oracle Database

I only see those relevant below:

---SAS/ACCESS Interface to PC Files 31DEC2014 

---SAS/ACCESS Interface to MySQL 31DEC2014 

does that mean I have no access to Oracle?

Solution
‎02-09-2015 09:47 PM
SAS Super FREQ
Posts: 8,820

Re: SAS Studio - link to Oracle Database

Hi:

  You are correct that without the SAS/Access modules you cannot connect to your Oracle database directly. SAS University Edition and SAS Studio are intended for learning purposes for many of our basic programming and statistical analysis classes. We do not cover SAS/ACCESS to Oracle in our programming classes, however, you can read delimited files, such as tab-delimited files, comma-delimited files and ASCII text files using SAS Studio and you can use PROC IMPORT to read Excel files.  If you have a full installation of SAS with the correct "helper" modules, you would be able to connect to an Oracle table, just as you do with R. But your syntax would be different, as shown here in the document with the LIBNAME statement for Oracle:

libname mydblib oracle user=testuser password=testpass path=hrdept_002;

  

proc print data=mydblib.employees;

   where dept='CSR010';  

run;

as shown in this documentation example:  SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

  But this will not work in SAS Studio, since you do not have the SAS/ACCESS to Oracle "helper" module. So you will have to look to other techniques to read your data, such as exporting from Oracle first (possibly as a delimited file) and then importing that file into SAS. In addition, the document you referenced on Data Administration for the SAS Platform is meant to be used when you are defining metadata and setting up libraries for the SAS Platform for Business Analytics (also known as the BI Platform), so without the rest of the Platform infrastructure, this is probably not the manual you should use for reference with SAS Studio. We cover explicit examples of using SAS Studio in our free Programming 1 and Statistics 1 e-learning classes. And, each course comes with a setup section that has a program where you can make data that can be used for analysis.

  To find the links to the free training, just go here: SAS Tutorials | SAS Training and look at the 2 links on the lower right-hand corner of the "New to SAS" page.

cynthia

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1199 views
  • 6 likes
  • 3 in conversation