Connecting to SAS Server through ODBC

[sabid]

Hi. I am wanting to connect to our SAS server through an ODBC connect. I need access to data for a report in Excel. Can anyone tell me how to do this? Do I need a 32 or 64 bit driver? What port do I connect to? 

Thanks!

[SASKiwi]

Do you have SAS/ACCESS to PC Files? If so the SAS Add-in to MS Office will provide exactly what you want.

Using ODBC to connect from a third-party product to a SAS server requires SAS/Share on your SAS server. Installing the SAS ODBC driver isn't enough.

[sabid]

Does this allow for Power BI access as well? I would like to create some visualizations. Also what other than the driver would be needed. 

Secondly, doesnt Excel import limit the size of file?

[SASKiwi]

There is no SAS Add-in for Power BI. We have found the easiest way to get data into Power BI is to upload SAS tables into SQL Server using SAS/ACCESS to ODBC (SAS/ACCESS to SQL Server is another option) then get Power BI to load the data from SQL Server.

With Excel you would be restricted by number of rows. It is possible to bypass row limits by feeding your SAS table directly into an Excel pivot table though if you only need summary data.

[sabid]

Hmmm. ok, thanks for this. Is there some documentation you could suggest for this? A how to sort of a guide? I was hoping for a direct connection to avoid the long chain of data movement from system, but if there is no other way, then am willing to try this.

Thanks again!

[SASKiwi]

Documentation for what? Setting up a connection to SQL Server with SAS/ACCESS to ODBC? If so a lot depends on what OS your SAS server runs on.

[Tom]

---

@sabid wrote:

Hi. I am wanting to connect to our SAS server through an ODBC connect. I need access to data for a report in Excel. Can anyone tell me how to do this? Do I need a 32 or 64 bit driver? What port do I connect to? 

Thanks!

---

Your need to explain more about what environment you have and what you want to do.  A SAS server is NOT a database engine.  It is a computer for running SAS code.  So to connect to it using ODBC does not make much sense without more details about what in particular you are trying to do. 

SAS datasets are files that live on your filesystem somewhere.  Do you just need to read a SAS dataset? Can you just convert the dataset into the native format used by whatever software you are running?  R and Python both have packages for reading SAS datasets.  What type of SAS installation do you have?  Are you running a SAS/Share server?  That would look more like a traditional database that you could connect to.  Are you running SAS workspace server?  Just running SAS on your individual PC?  Just running regular SAS (what they used to call BASE SAS or now call FOUNDATION SAS) on a multiuser operating system (like LINUX or IBM OS)?

Do you need to connect to a running SAS session that already has predefined librefs pointing to different data libraries and access some of those libraries.  Or do you need to be able pass it the information about where to find the particular dataset(s) that you want to read?