DATA Step, Macro, Functions and more

SAS and Azure SQL DAta warehouse/Database

Reply
Occasional Contributor
Posts: 19

SAS and Azure SQL DAta warehouse/Database

Dear SAS Users,

I have set up a DSN that allows me to connect to Azure using a libname.

 

LIBNAME EREKA_AZ ODBC DSN = 'MYDB' SCHEMA='THESCHEMA';

 

Is there a way to translate this so I can use SQL PAss through?

 

 

 

PROC SQL NOPRINT;

CONNECT TO ODBC (

DSN = "MYDB"

SCHEMA = 'THESCHEMA'

);

create table test as select * from odbc(

select * from le_table

);

DISCONNECT FROM ODBC;

QUIT;

 

 

Super User
Posts: 3,252

Re: SAS and Azure SQL DAta warehouse/Database

Posted in reply to shwilliams4

If you can successfully connect using a LIBNAME then you should be able to do the same with a CONNECT statement in PROC SQL. Please post the log of your SQL PASSTHRU test so we can see what errors you are getting.

Occasional Contributor
Posts: 19

Re: SAS and Azure SQL DAta warehouse/Database

I was making an error somewhere. I am able rto connect via the dsn. Thank you.

Occasional Contributor
Posts: 19

Re: SAS and Azure SQL DAta warehouse/Database

Posted in reply to shwilliams4

Back to being unable to connect.

libname AZsand ODBC DSN="EReKA DB Sandbox";

NOTE: Libref AZSAND was successfully assigned as follows:

Engine: ODBC

Physical Name: EReKA DB Sandbox

 

But no tables appear in the libname.

 

However,

 

PROC SQL NOPRINT;

CONNECT TO ODBC (

DSN = "EREKA DB Sandbox"

);

create table az_counts as select * from connection to odbc

(

select ereka_model_seq,count(*) as counter from [dbo].[ereka_model_data]

group by ereka_model_seq

);

DISCONNECT FROM ODBC;

QUIT;

 

Works. So the DSN is working, just not for a libname.

I have tested the conenction through ODBC-32 bit and SQL Server Management Studio.

 

Ask a Question
Discussion stats
  • 3 replies
  • 353 views
  • 1 like
  • 2 in conversation