SAS Connect to SQL Server - ODBC

0
votes

I'm trying to connect to several SQL servers with raw SQL tables using SAS and ODBC connections. I have managed to do this by setting up my ODBC connections and running the below code however I have a couple of questions and potential requirements before I can move on, so here goes....

LIBNAME libname ODBC DSN='SQL Server' SCHEMA=dbo READ_LOCK_TYPE=NOLOCK;

  1. I ran the above in base SAS and got the library with the little globe as expected and how I would like. When I ran in Enterprise guide there is no globe and the library is under the local server. Would this indicate the data is now local and not on the original server via ODBC? If it is local is there a way to link to the data via ODBC as I do not want the raw data to be local?

  2. When setting up the SAS libraries in enterprise guide is there a way to create sub libraries so I can construct in the same way as SQL server management studio? For example...

    • Server
      • Database
        • Table

I can create the tables from each database in one library but due to the size of the SQL server it will mean allot of libraries and also make them difficult to name due to the lib name length restrictions in SAS

  1. Finally is there a way to specify multiple schema names so different schema names can reside under one library?

Eg dbo.table1 & test.table1 (Where they are the same table name but different tables due to the schema name?)

I really hope someone can help! I appreciate this maybe a big question. I have done a good deal of searching around the internet and on stack overflow and could not find an answer.

Many many thanks in advance!

1
sql-serversasodbc
Why is your question tagged mysql? – Parfait

1 Answers

0
votes

I'll answer part of your question.

  1. No, the data is still on the server, even though it shows as local.

  2. No, Only single level libraries are supported.

  3. I'm not 100% sure on this, I know you can assign multiple libraries with paths, so you could try adding in multiple definitions and see if that works, or some variant with the libname function (not statement).