Importing SAS data into PowerBI

Data analysis without visualization is almost useless and to many (end-)users Microsoft Power BI is just the easiest to use, freely available tool around. While the built-in PowerQuery data access feature provides access to a lot of different types of data sources, SAS datasets is, however, not one of them.

But while there is no built in native adapter for SAS data sets in Power BI (which a good many people seem  to be asking for) the “R script” adapter can come in quite handy as a workaround!

For R there are a couple of packages which will read SAS data:  “sas7bdat” and “haven”. It seems that “sas7bdat” is quite slow and as “haven” is the choice in RStudio for importing SAS data, I took that as a seal of approval and went with “haven”.

When “R script” is selected as the data source in PowerBI a script window pops up like so:

image

Here are the few lines of script for my sample:

if (!require(“haven”)) {
   install.packages(“haven”)
}

require(haven)
data_file <- “c:/Users/peh/Documents/SAS/aregn03.sas7bdat”;
sasdata <- read_sas(data_file, catalog_file = NULL, encoding = NULL)

Simply edit the SAS file name (“data_file” – remember to fix the slashes in the path name to R-standard) in the script to suit your own file-path and press OK. Now the Power BI navigator will ask which of the R dataframes in your script to import. Here there is only one (“sasdata”) so select that and click OK and your data will be loaded into the Power BI data model.

The usual memory limitation of R will of course apply to the SAS data being loaded, but in most of cases I think this method will be quite sufficient.

Of course you could export your data from SAS itself to a variety of PowerBI compatible formats, or use R to convert it to ie CSV before loading into Power BI, but I think the above method is quite convenient if you’re a SAS shop and an easy template to communicate even to people without prior knowledge of either SAS or R.

Advertisements

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