Solved

Reading SQL Server 2008 data using SAS9.1

Posted on 2011-03-08
11
1,144 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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