Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reading SQL Server 2008 data using SAS9.1

Posted on 2011-03-08
11
Medium Priority
?
1,153 Views
Last Modified: 2013-11-16
We have a a database on SQL Server 2008 and we just hired a SAS programmer to do some statistics on the data.
We would like to linked both Software so SAS can read the data on the SQL Server 2008.
by the way, the server is running Windows Server 2008.
Thank for any help.
0
Comment
Question by:LougaLo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 15

Assisted Solution

by:MohammedU
MohammedU earned 1000 total points
ID: 35085637
You can use OLEDB connection string..
You have to create a sql login IN SQL 2008 and use that in the oledb connection string or the datasource used by the SAS.
oledb provider=sqloledb init_string=’Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myusername Catalog=my_database_name\my_instance_name;Data Source=database_server_name’ schema=dbo ;  etc...
0
 
LVL 7

Expert Comment

by:d507201
ID: 35089770
I don't work with SQL Server, but I think you have to license SAS/Access for Relational Databases to get Access for OLE DB. In the same bundle you also get SAS/Access for Microsoft SQL Server.  Use that.  Native access is always more efficient especially if you take advanatage of the libname options.  

I work in UNIX and use SAS/Access for Teradata, DB2 and Oracle. The libref options make all the difference in the world in terms of performance..
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35090096
depends on which server you are running your base SAS from, you'll need SAS/Access to ODBC (on Windows) or SAS/Access to MSSQL (for non-Windows).

for Windows base SAS, you just need to configure an ODBC connection using Window's Data Sources (ODBC) from the control panel, and use it in a LIBNAME statement.

for non-Windows, i'm not able to advise as I have never tried on that before.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:LougaLo
ID: 35153820
i was able to connect the database with the SAS through ODBC connection. but I am failling to read any data from the database. I tried the sas LIBNAME but it is not working.
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35154138
what's your libname statement like?

did you define the schema name in the ODBC connection setup?
0
 

Author Comment

by:LougaLo
ID: 35308770
yes the schema name is already defined.

here I am using the Windows Authentication to log in the SQL server. I did not set up any password and username.
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35308795
can you post your libname statement? did you mean to say that when you tried executing the libname statement it fails? if so, what do you mean when you say "i was able to connect the database with the SAS through ODBC connection."?

what is the schema you are connecting to (when you set up the ODBC connection)?
0
 

Author Comment

by:LougaLo
ID: 35309676

lowaloysius:
I clicked on Settings --> Control Panel and choose Administrative Tools.
From there, choose Data Sources (ODBC).

The driver has been configured and the test connection is successful.
This is  where I get stuck.

it seems I need to create a library within SAS in order to read tables in sql server.
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35309753
do you have something like:
LIBNAME [some name] ODBC DSN=[your ODBC source] SCHEMA=[the schema name in the database];

in your codes?
0
 

Author Comment

by:LougaLo
ID: 35311540
login to local SQL Server through Windows Authentication

here is the statement

LIBNAME SQL ODBC DSN=’SasODBC’ user='' pw=pwd schema=dbo;

error
ERROR: Libname SQL is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name SASODBC.
0
 
LVL 14

Accepted Solution

by:
Aloysius Low earned 1000 total points
ID: 35312541
how would the following do for you:
LIBNAME SQL ODBC DSN="SasODBC" user='' pw=pwd schema=dbo

what i have done is to change from single quotation marks to double quotation marks

one other thing i'm not sure of is whether you can use the word SQL as the library name. could you try something else?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question