SAS Procedures

Help using Base SAS procedures
BookmarkSubscribeRSS Feed
AlanC
Barite | Level 11

I am trying to connect to the Azure Cosmos database. I have a url:

 

https://mydb.azure.com

 

And an AccountKey: ABC123

 

Any ideas on connecting? This is a NoSQL database.

https://github.com/savian-net
3 REPLIES 3
pink_poodle
Barite | Level 11

The connection works through ODBC (Open Database Connectivity). Here is a post discussing that:

https://communities.sas.com/t5/SAS-Programming/Experience-with-SAS-and-Cosmos-DB/td-p/479398

 

LIBNAME mydblib ODBC DATAsrc=azuresrvr1 USER=testuser PASSWORD='ABC123';

ReferenceLIBNAME Statement Specifics for ODBC

NZ_Hockey_Mum
Calcite | Level 5

I have managed to get

  • Cosmos DB drivers installed (from CDATA) on my SAS comp server
  • Got a system DSN ODBC connection set up on my SAS comp server (that tests as working) using the URL and token
  • LIBNAME cosmos ODBC DATAsrc=CosmosDBODBCNAME ; (Syntactical works)

 

But that is as far as I have got.  I do not know what the collections are called on the database.

 

Has any one found as examples of how to discover what collections are on a Cosmos nosql database?

 

Not from within SAS but using https://azure.microsoft.com/en-us/features/storage-explorer/

 

NZ_Hockey_Mum
Calcite | Level 5

Update whilst the libname is syntactical correct it does not appear to work.

 

If I use the pass through syntax I get error "schema map entry for "tablename" could not be found. Check query syntax and schema map definition and try again." 

 

Any ideas?

 

Got it working ....

 


* first get the schema and table names using .... ;
proc sql;
connect to odbc(DATAsrc=ODBC_DNS_NAME);
create table work.odbcSchema as
select * from connection to odbc
(ODBC::SQLTables);
quit;

* the output has the schema name labelled as Table_schem and the table_name in my database ;

* I got field name from using Microsoft Azure Storage Explore ;

proc sql;
connect to odbc(DATAsrc=ODBC_DNS_NAME);
create table work.SAS_dataset_name as
select * from connection to odbc
(select fieldName from Table_schem.table_name);
quit;

 

(note :S is actually a colon followed by a S)

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1816 views
  • 0 likes
  • 3 in conversation