topic Re: SAS University Edition: Can I access data in an MS SQL Server database? in SAS Programming

SAS University Edition: Can I access data in an MS SQL Server database?

john678321 — Sat, 28 Jun 2014 06:29:39 GMT

Hi,

I've just started kicking the wheels of the university edition. My notebook is running SAS UE in an Oracle Vitualbox hosted environment, and MS Sql-Server.

I'd like to access the database - is this possible with the University Edition?

I've found and tried some Library statements, but I get an error about a missing odbc library module on the Linux side.

Any help would be appreciated.

Chris.

Re: SAS University Edition: Can I access data in an MS SQL Server database?

kodmfl — Sat, 28 Jun 2014 16:20:38 GMT

Chris

I don't work in Linux but here are a couple of things that I found were important for gaining access to an Oracle database on our network. I needed whatever an ODBC driver installed which I later modified to specifications of the database. I had to have Oracle Client installed which is free. You can go to their website and get whatever version you need. Your IT would know which one you would need and they should be able to help you with all of this. Once I got the client I had to update the file called tsnames.ora. I had to change things like the server name, port, and some other stuff based on input from our IT department. My ODBC drivers reflect the information in tsnames.ora and the SAS code I use which looks something like this:

connect to odbc (dsn='xxxxx' uid=xxxxx pwd='xxxxxxxx');

create table tablexxxxxx as select * from connection to odbc (select * from ext.tablename);

Finally I suspect you'll need a TCP/IP connection to the server where the data are located. You need IT support for this I would think.

I think this might help. No guarantees

Re: SAS University Edition: Can I access data in an MS SQL Server database?

Cynthia_sas — Sat, 28 Jun 2014 20:33:56 GMT

Hi:

I believe that the University Edition comes with SAS/Access to PC Files, per the key features shown on page 4 of this product information sheet:http://www.sas.com/content/dam/SAS/en_us/doc/factsheet/sas-university-edition-107140.pdf.

I think that to use PROC SQL and a connection to ODBC you need SAS/Access to ODBC, as shown here in the Tech Support note about the System Requirements for using SAS/Access to ODBC 11077 - System Requirements for SAS/ACCESS to ODBC for SAS 9.1 on Windows64-bit platform. I do not think the University Edition comes with SAS/Access to ODBC -- it's not listed on the product fact sheet. You might have to investigate other ways of getting to your MS-Sql Server data, such as exporting the data from MS-Sql to one of the listed file types that can be easily read with SAS/Access to PC Files.

cynthia

Re: SAS University Edition: Can I access data in an MS SQL Server database?

kodmfl — Sat, 28 Jun 2014 21:26:26 GMT

Cynthia thanks for updating John, I wasn't aware of the limitation you mentioned in the University Edition.

Re: SAS University Edition: Can I access data in an MS SQL Server database?

john678321 — Sun, 29 Jun 2014 01:33:25 GMT

Cynthia,

I expect that you are correct, that is, University Edition does not support ODBC data sources. That's unfortunate for me, because my test data is in MS SQL-Server. I could export it to a spreadsheet, but that is inconvenient, onerous and doesn't help me build skills required for corporate environments. Still it's offered for free, so I'll have to accept the situations.

Thanks for your help.

Chris.

Re: SAS University Edition: Can I access data in an MS SQL Server database?

GenDemo — Mon, 04 Nov 2019 00:08:21 GMT

I have the same sentiment here... but I think one should bear in mind what SAS University Edition is intended for.
It is an awesome program (as I am sure we all here in the community know full well) and they need to make sure that University Edition is not used for corporate gain. My take on it is, that as soon as as you have a SQL server running, then it is a very strong indicator that you are in a corporate environment and should not be using University Edition.
But yes, I too have SQL running on my local machine so that I can practice and hone my skills. And it is unfortunate that you can't connect SAS University Edition to it... but I feel there is a good enough reason for it.

Just my 2c worth.