Reading SAS (*.sas7bdat) Files In Power BI

2 comments

Since Power BI does not have a native connector to SAS yet I decided to write this blog and walk you through using R to read data from your *.sas7bdat files.

I will assume that the reader does not have R installed so I will walk you through all the steps necessary to get this going.

Step 1 – Install R

You can follow this link https://cran.r-project.org/bin/windows/base/ and then click on Download R link. The executable will give you an option to install both 32 and 64 bit binaries so please make sure that you match it to your version of Power BI Desktop

Step 2 – Make sure R is enabled in Power BI Desktop

After R has been installed, launch Power BI Desktop and click on File->Options and Settings->Options->R Scripting and make sure that Detected R home directories is populated with the path of your R installation.

Step 3 – Install sas7bdat R library

Launch the R GUI. In my Windows 10 laptop I type MRO in the search box and the R GUI executable pops up

At the command prompt type: install.packages(“sas7bdat”)

If you are connected to the internet the library will be installed and your screen will look like the following:

Step 4 – Read in your file

Now we are ready to connect Power BI Desktop to you file. Click on Get Data->More->R Script

Then paste the following R script into the window (replace my path to the file with your own)

require(sas7bdat)

input<-read.sas7bdat(“C:/help.sas7bdat”)

And then click OK (please make sure that your double quotes are in fact double quote)

In the Navigator window select Input and click Edit

You now should be taken to the Power Query window where you can do all necessary transformations

Please note, that SAS dates may come in as integers so you will need to add 21916 to the number and then convert it to the Date type.

2 comments on “Reading SAS (*.sas7bdat) Files In Power BI”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s