Sas connection to powerbi

[saikiran43]

How to connect sas with powerbi??How to read sas file data in powerbi

[AndrewHowell]

Two-fold response:

• In what format do you store data which PowerBI can read? I don't know (Microsoft) PowerBI, so I'll assume it cannot natively read SAS data sets, in which case the data will need to be saved to a more common data format (or MS-compatible) database.
• Side-by-side with format is location: where do you store the data so that powerbi can access it? Can PowerBI access the filesystem where the SAS data set resides?

 To help us to help you, please tell us more about your SAS installation - what products you have installed, etc?

[LinusH · Accepted solution]

Usually, third party vendor SW cannot read SAS files directly, tablau being an exception.

So you need either a SAS user export the data to format convenient for you, like CSV?

If you need a more robust and constant solution, you can read SAS data via ODBC, which requires a local SAS installation, or a SAS Server running SAS/SHARE or SAS SPD Server.

[saikiran43]

Actullay i retrived  the data of  sas file using R script,can u tel me how to read sas using odbc

[AndrewHowell]

It assumes you have "SAS/Access to ODBC" licensed, and depends on how you have ODBC configured, but if both are there, then it should be as simple as:

libname MyLib odbc dsn="ODBC settings";

.. give or take a few additional parameters (username, password).

Suggest you read:

• SAS 9.4 Drivers for ODBC User’s Guide
• Jeff Bailey's SGF paper: An Insider’s Guide to SAS/ACCESS Interface to ODBC

[minallk]

I tried going through ODBC route , but no success . Not able to view the data in Power BI .

Can you please explain ..

Also I don't have any parameter  values for the User Name and passwored for SAS datasets .. from where I will be able to fetch that ?

[Anil_Gutlapally]

Hi LinusH, Could you please help me understand how to get SAS data into PowerBI using ODBC/ SAS Share? We do have SAS Grid server with SAS SHARE but couldn't get the connection work with PowerBI. Thanks, Anil

[Raju4234]

Hi LinusH,

I am not a SAS expert.

Our business has a SAS share and I want to connect to SAS share read the data sets via ODBC Connection. I am able to do this in Excel but not in Power BI. Is there a way we can do this or I need to install SAS. I have tried with colleagues that have SAS installed but it does NOT work.

Regards

Raju

[GenDemo]

I am also eagerly waiting for them to build in a connection to SAS files or direct to SAS server.

I use R-scripts to connect as in our environment at work, I cannot create ODBC connections to the files.

1. Install R

2. Get the sas7bdat library and install that into R

3.  in Power BI, connect to data with an R script and use the following script (remember that R uses linux convention of forward slashes, not back slashes):

          library(sas7bdat)

         require(sas7bdat)

         input<-read.sas7bdat("Z:/network/project/mysasfile.sas7bdat")

[sudheer]

Hi ,

Is there any solution which we can read sas data set into power bi.

Regards

Sudheer

[GenDemo]

You can use R
I dont have the scrupt with me, but I can share it tomorrow when I’m back in the office. The solution works rather well.

[GenDemo]

Hi

so, yes, you can. Its a little bit of a process to set up. however it seems to work well.

1. Download and install R - the later the version the better. I have 3.4.3.

2. download and install the sas7bdat library for R.

   This can be done through R, by using the command install.packages("sas7bdat")

   This will download the files to some downloads folder. R will tell you where.

   I am no R wizard, so the easiest, I have found, is to then browse to the location. Under the data folder, there is a file         'sas7bdat.sources.RData'. Drag and drop this file into R, and it will install it.

   You can close R at this point.

3. Open PoweBI. Under settings, check that it picked up your R installation.

   if not, you will need to point it to the right folder.

4. click 'get data' and choose the 'R script' option under other.

5. the script you'll us is:

library(sas7bdat)

require(sas7bdat)

input<-read.sas7bdat("Z:/folder/folder/my_SAS_file.sas7bdat")

   remember that R, like Linux, uses '/' in its file paths, not '\' as in Windows.

6. This script will pull in your data and you'll be able to refresh it as normal.

Good Luck!

GD

[Rohan1]

Hi, 

Thank you for the solution. I haven't tried this at my end as I don't have the R installed in the system (Have requested the admin). Meanwhile, I want to know if you have come across the conflict in date conversion between SAS and PowerBI. I had this issue (in 2016) when I tried to use SAS dataset on Excel due to different beginning dates they have (1900, 1960), and the solution then was to convert the SAS date into characters (Not a great solution). Do you know a solution for this? or has this been overcome in PowerBI?  

Thankfully,

Rohan

[GenDemo]

Hi Rohan

Yes. I know of this problem, I used to always use the character conversion as well. However fairly recently I had to start using actual dates. Last I checked, this has not been overcome in PowerBI yet. There is great to get a proper connector between SAS and PowerBI, but I suspect there is some corporate disagreement or something. This problem is also prevalent if you connect Excel directly to the SAS files. I can't remember if you use SAS to store data on a SQL server Db and then connect Excel/PowerBi to that, if this problem also exists - its been a while since I could do that, so I can't recall.

So what I do is one of two things,

 1. After you final data step (or what ever it may be) convert all you date column to excel by subtracting 21916 (that is the number of days difference in their reference date).

      e.g. Loan_application_date = Loan_application_date - 21916;

 2. Create a new date variable.

      e.g. Loan_application_date_Excel = Loan_application_date -21916;

However, I do believe that if you export your files to an excel file, it does the conversion for you - but I am not sure off the top of my head. Also, if you use the SAS plugin, then it also does the conversion for you. I however don't use the plugin to directly connect to data as it does not link in/hookup to the 'refresh all' function of Excel.

Should you want to make the time conversion - as SAS works in seconds after midnight, while Excel works in fractions of a day. This can be achieved in a similar means:

 1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).

      e.g. Loan_application_time = Loan_application_time / 86400;

 2. Create a new date variable.

      e.g. Loan_application_time_Excel = Loan_application_time / 86400;

Should you want to make the datetime conversion.

This can be achieved in a similar means:

 1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).

      e.g. Loan_application_time = (Loan_application_time / 86400) - 21916;

 2. Create a new date variable.

      e.g. Loan_application_time_Excel = (Loan_application_time / 86400) - 21916;

then obviously, you can do the reverse as well if you need to take Excel date/time/datetime to SAS.

Also, FYI SAS & SQL have the same reference date and time units.

Hope this helps

~GD

[Rohan1]

Hi ,

Thank you for your prompt reply. Very useful tips. I too have done similar
modification on the integers using VBE as my reports are automated..
However, the SAS plug-in through Excel doesn't fix the issue automatically
though SAS Excel export does it.

Regards,
Rohan