Solved

Reading SQL Server 2008 data using SAS9.1

Posted on 2011-03-08
11
1,145 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 250 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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