topic Re: SAS connect with MS SQL Server in SAS Programming

SAS connect with MS SQL Server

vijaypratap0195 — Sat, 27 May 2023 10:38:06 GMT

I am trying to connect MSSQL Server with SAS using the following:

1. SQL Pass-Through, or explicit pass-through

2. LIBNAME statement, or implicit pass-through

But I am unable to make a connection between them.

Here are my MSSQl details (I am trying to do this using Windows authentication)

Can anyone help with the complete code I can use in my SAS EG to connect SAS with MSSQL?

-Vijay

Re: SAS connect with MS SQL Server

ballardw — Sat, 27 May 2023 21:21:41 GMT

First run this code and then show us the result from the log:

Proc setinit; 
run;

Go to the log and copy the results starting at Product Expiration dates and then post into a text box opened on the forum using the </> icon above the main message window. You should see something like:

Product expiration dates:
---Base SAS Software
      30JAN2025
---SAS/STAT
      30JAN2025

The output shows what products you have licensed to use. If you don't have SAS Access/SQL Server (or whatever name actually comes up) you may be able to access data through the ODBC connection but that has different limits and abilities than the SQL Server package.

If your SAS is running EG on a server then the links would have to be available to server. It would not see any local connection you may have to SQL server and likely need to get your SAS admin involved in getting the mappings set up.

Re: SAS connect with MS SQL Server

SASKiwi — Sat, 27 May 2023 23:06:15 GMT

Where is your SAS installed? On a server or on a PC? What SAS/ACCESS product are you using? SQL Server or ODBC? Please post the SAS logs for your two attempts including any errors.

Re: SAS connect with MS SQL Server

vijaypratap0195 — Sun, 28 May 2023 16:30:39 GMT

I have installed in my PC

Re: SAS connect with MS SQL Server

SASKiwi — Sun, 28 May 2023 19:55:51 GMT

Please post the SAS logs from your two attempts.

Re: SAS connect with MS SQL Server

Quentin — Sun, 28 May 2023 21:38:02 GMT

Since you mention EG and you have SAS/ACCESS to SQL Server, that makes me think that EG (which might be running on Windows) is connecting to a linux SAS server. Is that right?

If so, then you won't be able to use Windows authentication, because the connection is made from the linux server where the SAS code executes to the database.

For connecting from a SAS server, connecting to a new SQL server would typically involve having a SAS server administrator update the odbc.ini file on the server which defines the ODBC connections, and the administrator might also create a SAS library for you. I would suggest working with your server administrator. Or do you have admin privileges on the server?

But then you also said you have "installed on your PC." Are you actually running SAS on your local PC (not a server), and using EG to connect to the SAS session on your PC?

Re: SAS connect with MS SQL Server

Tom — Sun, 28 May 2023 22:02:36 GMT

That photograph (why take the time to make a picture out of text?) you posted appears to be from SAS running on some server. When I run SETINIT on SAS running on PC it does not mention anything about CPU's.

Product expiration dates:
---Base SAS Software                                                                                    31OCT2023
---SAS/STAT                                                                                             31OCT2023
---SAS/GRAPH                                                                                            31OCT2023
---SAS/ETS                                                                                              31OCT2023
---SAS/FSP                                                                                              31OCT2023
---SAS/OR                                                                                               31OCT2023
---SAS/AF                                                                                               31OCT2023
---SAS/IML                                                                                              31OCT2023
---SAS/QC                                                                                               31OCT2023
---SAS/SHARE                                                                                            31OCT2023
---SAS/ASSIST                                                                                           31OCT2023
---SAS/CONNECT                                                                                          31OCT2023
---SAS/EIS                                                                                              31OCT2023
---SAS/SHARE*NET                                                                                        31OCT2023
---MDDB Server common products                                                                          31OCT2023
---SAS/Secure 168-bit                                                                                   31OCT2023
---SAS/Secure Windows                                                                                   31OCT2023
---SAS Enterprise Guide                                                                                 31OCT2023
---OR OPT                                                                                               31OCT2023
---OR PRS                                                                                               31OCT2023
---OR IVS                                                                                               31OCT2023
---OR LSO                                                                                               31OCT2023
---SAS/ACCESS Interface to Oracle                                                                       31OCT2023
---SAS/ACCESS Interface to PC Files                                                                     31OCT2023
---SAS/ACCESS Interface to ODBC                                                                         31OCT2023
---SAS/IML Studio                                                                                       31OCT2023
---SAS Workspace Server for Local Access                                                                31OCT2023
---High Performance Suite                                                                               31OCT2023

Compared to running in SAS on UNIX server:

---Base SAS Software                                                                                    30DEC2023 (CPU A)
---SAS/STAT                                                                                             30DEC2023 (CPU A)
---SAS/GRAPH                                                                                            30DEC2023 (CPU A)
---SAS/ETS                                                                                              30DEC2023 (CPU A)
---SAS/FSP                                                                                              30DEC2023 (CPU A)
---SAS/OR                                                                                               30DEC2023 (CPU A)
---SAS/AF                                                                                               30DEC2023 (CPU A)
---SAS/IML                                                                                              30DEC2023 (CPU A)
---SAS/QC                                                                                               30DEC2023 (CPU A)
---SAS/SHARE                                                                                            30DEC2023 (CPU A)
---SAS/CONNECT                                                                                          30DEC2023 (CPU A)
---SAS/SHARE*NET                                                                                        30DEC2023 (CPU A)
---SAS/IntrNet                                                                                          30DEC2023 (CPU A)
---SAS Integration Technologies                                                                         30DEC2023 (CPU A)
---SAS/Secure 168-bit                                                                                   30DEC2023 (CPU A)
---SAS/Genetics                                                                                         30DEC2023 (CPU A)
---SAS Enterprise Guide                                                                                 30DEC2023 (CPU A)
---OR OPT                                                                                               30DEC2023 (CPU A)
---OR PRS                                                                                               30DEC2023 (CPU A)
---OR IVS                                                                                               30DEC2023 (CPU A)
---OR LSO                                                                                               30DEC2023 (CPU A)
---SAS/ACCESS Interface to Oracle                                                                       30DEC2023 (CPU A)
---SAS/ACCESS Interface to PC Files                                                                     30DEC2023 (CPU A)
---SAS/ACCESS Interface to ODBC                                                                         30DEC2023 (CPU A)
---SAS/ACCESS Interface to Microsoft SQL Server                                                         30DEC2023 (CPU A)
---Grid Manager for Platform                                                                            30DEC2023 (CPU A)
---SAS/IML Studio                                                                                       30DEC2023 (CPU A)
---SAS Workspace Server for Local Access                                                                30DEC2023 (CPU A)
---SAS Workspace Server for Enterprise Access                                                           30DEC2023 (CPU A)
---SAS/ACCESS to Amazon Redshift                                                                        30DEC2023 (CPU A)
---High Performance Suite                                                                               30DEC2023 (CPU A)
---SAS Add-in for Microsoft Excel                                                                       30DEC2023 (CPU A)
---SAS Add-in for Microsoft Outlook                                                                     30DEC2023 (CPU A)
---SAS Add-in for Microsoft PowerPoint                                                                  30DEC2023 (CPU A)
---SAS Add-in for Microsoft Word                                                                        30DEC2023 (CPU A)
---SAS/ACCESS to Snowflake                                                                              30DEC2023 (CPU A)

I am not sure if using Windows Authentication can work from Unix.

Re: SAS connect with MS SQL Server

ballardw — Mon, 29 May 2023 15:17:22 GMT

@vijaypratap0195 wrote:

I have installed in my PC

That actually only shows that the product is licensed.

Proc product_status;
run;

will verify that the modules are actually installed.

The log looks something like this for each product installed:

For Base SAS Software ...
   Custom version information: 9.4_M4
   Image version information: 9.04.01M4P110916
For SAS/STAT ...
   Custom version information: 14.2
For SAS/GRAPH ...
   Custom version information: 9.4_M4

You should be able to copy text from your log and then on the forum open a text box using the </> icon above the message window. Then paste in the text box. For one thing it should be faster than making an image and attaching, second it is easier to read and in the cases of syntax discussion, much easier to copy, correct or make suggestions and paste.

Re: SAS connect with MS SQL Server

vijaypratap0195 — Tue, 30 May 2023 07:02:28 GMT

I am able to establish the connection between SAS and MSSQL using this:

libname SQLSRVR odbc noprompt = "Driver=SQL Server;server=VIJAY_PRATAP\SQLEXPRESS;Trusted Connection=yes" DATABASE=test schema=dbo;

Connection test:

%put %sysfunc(ifc(%sysfunc(libref(SQLSRVR)) = 0, "Connection successful", "Connection failed"));

proc datasets lib=SQLSRVR ;
quit;

I can see only these many datasets/Tables.

In MSSQL my table is located here in test database:

Can you please tell me how can I fetch the Table data in SAS here?

I tried :

proc print data=SQLSRVR.MyTable; run;

Re: SAS connect with MS SQL Server

Quentin — Tue, 30 May 2023 12:28:34 GMT

Instead of using trusted_connection=yes, maybe try assing user ID and password in the connection string.

Also what happens if you run:

data sqlsrvr.foo;
  x=1;
run;

Does the SAS log say that it successfully created the table? Can you see the table in SQL Server Management Studio?

Re: SAS connect with MS SQL Server

vijaypratap0195 — Tue, 30 May 2023 13:12:25 GMT

It runs without an error:

data sqlsrvr.foo;
x=1;
run;

table got created in master database instead of test:

code:

libname SQLSRVR odbc noprompt = "Driver=SQL Server;server=VIJAY_PRATAP\SQLEXPRESS;Trusted Connection=yes" DATABASE=test schema=dbo;

%put %sysfunc(ifc(%sysfunc(libref(SQLSRVR)) = 0, "Connection successful", "Connection failed"));

proc datasets lib=SQLSRVR ;
quit;

data sqlsrvr.foo;
x=1;
run;

Re: SAS connect with MS SQL Server

AhmedAl_Attar — Tue, 30 May 2023 13:23:53 GMT

@vijaypratap0195

Try using SQL Passthrough

Proc SQL;
connect using sqlsrvr as sqlsrvr;
create table <mytable> as
select * from connection to sqlsrvr (Select * from dbo.Mytable);
disconnect from sqlsrvr;
quit;

Here is paper for more details and illustration

Emulating FIRST. and LAST. SAS® DATA Step Processing in SQL? Concepts and Review (lexjansen.com)

Hope this helps

Re: SAS connect with MS SQL Server

vijaypratap0195 — Tue, 30 May 2023 13:28:24 GMT

code:

libname SQLSRVR odbc noprompt = "Driver=SQL Server;server=VIJAY_PRATAP\SQLEXPRESS;Trusted Connection=yes" DATABASE=test schema=dbo;

Proc SQL;
connect using sqlsrvr as sqlsrvr;
create table a as
select * from connection to sqlsrvr
(Select * from dbo.Mytable);
disconnect from sqlsrvr;
quit;

Re: SAS connect with MS SQL Server

Quentin — Tue, 30 May 2023 13:33:55 GMT

Great, so that shows that you're connected to the right server, but wrong database.

Are you able to pass the database name inside the connection string, something like:

libname SQLSRVR odbc noprompt = "Driver=SQL Server;server=VIJAY_PRATAP\SQLEXPRESS;Trusted Connection=yes;DATABASE=test" ;

? You might need to look up the connection string syntax for SQLEXPRESS. There are examples at https://www.connectionstrings.com/sql-server/ .

Re: SAS connect with MS SQL Server

vijaypratap0195 — Tue, 30 May 2023 13:38:46 GMT

Worked, Thanks