SAS/ACCESS

[SYREXOKONNA0]

Hi All,

I have recently convinced my organisation to get SAS  after a very long back and forth argument.

They have agreed to give me one desktop licence of analytic pro,I understand analytic pro is SAS EG.

I want to be able to connect to our Micro-soft SQL server and be able to Query our database directly through SAS tool.As well as Excel and flat file if need be.

What SAS/ACCESS would you recommend or is good for my organisation.

Because I am the one driving this,I will have to set the SAS analytic pro up to ensure it does what I want it to do.

I am a SAS user mainly base SAS, but I have not been involved in the administration side or configuration  of this tool before, but it will be a very valuable experience for me.

Therefore which of the following below is best for me and any code suggestion that optimise the performance of those listed below: 

1. SAS/ACCESS Interface to ODBC
2. SAS/ACCESS Interface to Microsoft SQL Server for Windows and UNIX (this includes SAS Access/ODBC and an ODBC driver for MS SQL Server
3. SAS/ACCESS to OLE D
4. SAS Add-in for Microsoft Excel      
5. SAS/ACCESS Interface to PC Files                                                                                                                                     

Also any expert advice will be welcome,as  i have to/Need to prove to my bosses that SAS is a good analytic and easy to use tool for the business

Thanks

[Reeza]

#4 is the Add in that allows you to use SAS from Excel directly. For users that do not use SAS it can be helpful but it's definitely not in the same category as the other components listed here.

My understanding is that SAS will come with Base, EG and Studio so you'll have your choice of tool to use. I would recommend Studio over EG these days (personally). 

I would say #5 is mandatory otherwise you cannot read Excel files. 

If you have #1 & #2 not sure if #3 would add much value beyond that. 

Make sure to get:

SAS/STAT - Statistical procs such as regression and such

SAS/ETS - if you deal with time series data at all

SAS/OR - optimization problems

SAS/GRAPHS - supports various graphics though SGPLOT removes the need. Maps and support for SHP files are only in SAS GRAPH though. 

[JBailey · Accepted solution]

Hi @SYREXOKONNA0

This is a complicated question. A proper answer would require an in depth discussion of what you are trying to accomplish and your environment.

Will your SAS server run on Windows or Linux?

Will you only be accessing Microsoft SQL Server or will you need to access Oracle, DB2, Hadoop, etc.

I am going to assume that you are only interested in access MS SQL Server and Excel... 

SAS/ACCESS Interface to ODBC

This is an extremely versatile product and will enable you to get to practically any data source for which there is an ODBC 3.5, or later, compliant ODBC driver. If your SAS server will be running on Windows this is a great choice because Windows ships with a MS SQL Server ODBC driver. Microsoft now ships an ODBC driver for Linux. Bulkload for MS SQL is supported here too. If your server will be running on Linux this choice loses some appeal. See the next response. 

SAS/ACCESS Interface to Microsoft SQL Server

This is a database specific product that is less versatile than ACCESS to ODBC. If your SAS server is running on Linux the included DataDirect ODBC driver makes things easier. Compare the ACCESS to ODBC and ACCESS to MS SQL Server doc to determine if the differences will mean a lot to you. For example: DBIDIRECTEXEC isn't supported here and it is in the ODBC product. I am sure there are other differences that I cannot recall of the top of my head.

SAS/ACCESS to OLEDB

I wouldn't consider using this one. ODBC is a much better choice. Microsoft is deprecating OLEDB.

SAS Add-in for Microsoft Excel

This product is useful if you are an Excel user and want to use Excel as your interface to to SAS. I believe it call SAS stored process but I may be wrong. I don't think this is something you are interested in. 

SAS/ACCESS Interface to PC Files

This product includes a LIBNAME engine for Excel files and could be quite useful. You may want to consider it.

You may find the following resources helpful:

SAS/ACCESS Doc

Insider's Guide to SAS/ACCESS Interface to ODBC

Insider's Guide to CREATE TABLE options (pay special attention to DBIDIRECTEXEC)

PC Files SGF paper - 2107 - https://support.sas.com/resources/papers/proceedings17/SAS0387-2017.pdf

I hope this helps. Let me know if you need more information.

Best wishes,

Jeff