Solved

Reading SQL Server 2008 data using SAS9.1

Posted on 2011-03-08
11
1,128 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now