topic Re: Connect to and query data from ODW in SAS Enterprise Guide

Connect to and query data from ODW

stuartward1 — Sat, 29 Sep 2018 19:43:37 GMT

I'm new to SAS and am looking for a step-by-step guide on how to connect to and query data from ODW. I'm able to successfully connect to and query data from EDWard, but only because someone in my team walked me through all the steps. Is there a document available for how to accomplish the connection setup to ODW? I'm very familiar with querying data from ODW using SQL Server Management Studio, so I not asking for help with the SQL part of the solution; just how to make the connection and any SAS syntax that needs to go around the actual SQL query.

Thanks,

Stuart

Re: Connect to and query data from ODW

Patrick — Sun, 30 Sep 2018 03:21:48 GMT

I don't know ODW but Googling and based on your statement that you can access it with SQL Server Manager I assume it's just a database on a SQL Server.

May be below paper will be helpful to you:

http://support.sas.com/techsup/technote/accessing-microsoft-sql-server-from-sas.pdf

Re: Connect to and query data from ODW

AndrewHowell — Sun, 30 Sep 2018 04:49:10 GMT

@stuartward1 - connecting how? Help us to help you by providing more information - connecting via SAS code, metadata connection, what SAS/Access products do you have, etc?

Re: Connect to and query data from ODW

SASKiwi — Sun, 30 Sep 2018 06:31:56 GMT

Virtually any query you have built in SQL Server Management Studio can be run unchanged in SAS by wrapping it in an SQL passthrough query that looks like the following. The SQL Server connection string you require should be known by your SAS administrator. Firstly we need to know what SAS product you are connecting with. The likely options are either SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC.

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (---- Put your SQL Server query here
   )
  ;
  disconnect from odbc;
quit;

Re: Connect to and query data from ODW

Reeza — Sun, 30 Sep 2018 22:45:12 GMT

1. Set up a DSN in your User DSN (ODBC Control Panel).

2. Use a libname to point SAS to the server. Then it shows up as a SAS libname and no issues needed.

libname demo ODBC dsn=myDSN schema=dbo;

I can't recall if the DSN or SCHEMA needs quotes, I think the paper someone else linked to above covers this in more details though.

@stuartward1 wrote:

I'm new to SAS and am looking for a step-by-step guide on how to connect to and query data from ODW. I'm able to successfully connect to and query data from EDWard, but only because someone in my team walked me through all the steps. Is there a document available for how to accomplish the connection setup to ODW? I'm very familiar with querying data from ODW using SQL Server Management Studio, so I not asking for help with the SQL part of the solution; just how to make the connection and any SAS syntax that needs to go around the actual SQL query.

Thanks,

Stuart

Re: Connect to and query data from ODW

stuartward1 — Fri, 26 Oct 2018 17:29:42 GMT

My apologies for the extremely long delay in replying and marking a solution. I appreciate all the replies to my question and the document that was provided was very helpful. And one additional apology for thinking this was the sub-community for just my company and not the entire SAS community (again, very new to SAS/community and that's the reason my acronyms wouldn't have made much sense; just for clarification, ODW is a SQL Server database).

There was just one additional tweak I'll mention that I needed to make for the connection to be successful... I used this syntax in my proc sql statement:

proc sql;

connect to SQLSVR as myODWtestCon(DSN=ODW user=XXXXXXX password=XXXXXXXX);

When I first tried "ODBC" (in place of SQLSVR), the connection would fail, but was successful with SQLSVR.

Thanks again to all that replied.

Stuart