Monday, December 12, 2016

Reading SAS files from Power BI using R Script

Recently I have been working to read SAS files (sas7bdat file format) in Power BI. SAS technical support and the Power BI community team have both worked with me to attempt to use the SAS Local Provider for OLE DB which is the only provider that does not require a SAS server.

We were able to use the SAS Local Data Provider 9.44 in Excel by using the Data Connection Wizard to access Data Link Properties and to read the sas7bdat data into Excel as a data source (see details on this at the bottom of this post).

However, following similar steps in Power BI lead to an error message because "the SAS Local Provider for OLE DB does not 'like' the GetOleDbSchemaTable method. (quote from SAS Technical support):
(Text of error: "The 'SAS Local Data Provider 9.44' provider is not registered on the local machine.")
Error Message
Text of error: "The 'SAS Local Data Provider 9.44' provider is not registered on the local machine."
Now, this error seems actionable until you remember that we just used the provider to connect from Excel. This is due to the use of the GetOleDbSchemaTable method and we could not find a way to work around this problem in Power BI.

I have submitted this as a support ticket to Microsof and I look forward to their response on how to get a supported connection to SAS files.

The next step is to find another way:
Loading Data into the Dashboard file using R Script:

The best method I have found so far to read SAS files into Power BI is using R script:
https://businessintelligist.com/2016/09/09/reading-sas-sas7bdat-files-in-power-bi/

1) Locate your SAS Data set files (you can download a sample set in ZIP format from: http://www.principlesofeconometrics.com/sas.htm)

2) Install R locally (I have been using R 3.3.2 from CRAN (https://cran.r-project.org/bin/windows/base/)

3) Install Power BI Desktop locally:
Download Power BI Desktop from http://app.powerbi.com

4) Open Power BI, and first, set your version of R in Options and Settings:
a. Click the “x” in the upper right corner of the splash dialog box to close it:
Close the Power BI Desktop dialog box
b.       Select File -> Options and Settings ->Options -> R Scripting
c.       You should be able to select your installed R version from the Detected R home directories list and Detected R IDEs
5) Now, setup the data source for your Power BI solution:
a.       Drop down Get Data and select More… -> Other –> R Script, then the Connect button
b.       The Execute R Script dialog box provides an input location for the following script which will set the working directory, access the library and read the file into Power BI:



c.       Click OK to see the content of the SAS file, select the table(s) to load  (multiple table files haven’t been tested by me using R Script)
d.       Select to Load the data directly, or Edit to use the Query Editor to filter, clean, reformat or otherwise transform the data for this and all future data loads.

6)      Click Close & Apply when the data set is complete and you are ready to use it in Power BI
 
Graph using Airline data from SAS file











Using a faster library:
I have found some information that haven is a faster library than sas7bdat, the sample files for SAS that I have access to are not sufficient to test this, but the haven library works very well:

Using haven the script changes to reference the haven library (https://cran.r-project.org/package=haven) and to use the read_sas function to read the SAS file from that library as you can see in this sample to read another data file, USA:
I am very interested to know what the results are using production size and complex datasets, and am willing to help in anyway that I can if you have questions about using this method.

Publishing and Refresh of data:
The Power BI solution can be published to http://app.Powerbi.com using the Publish button. The R Script has imported the dataset, so that will be published to the Power BI Service along with the reports.

R Script as a data source does not yet support refresh. There is a setting for refreshing data from Files, but that does not work for R Script as a source, it is for CSV, etc…


Rachael

33 comments:

Unknown said...

Thanks for sharing this informative blog to our vision. Keep update your blog. If anyone interested to know more details about SAS then learn SAS Training in Bangalore will definitely helps you.Data Science Training in Bangalore

venkat said...

Hats off to your presence of mind..I really enjoyed reading your blog. I really appreciate your information which you shared with us.
SAS Online Training
R Programming Online Training|
Tableau Online Training|

Ishu Sathya said...

Excellent article on the importance of R programming in tableau tool. I am working in the tableau related project. I gain some new updated regarding the tableau tool R Programming. Keep updating the recent updates of R. Thank you admin.

Regards:

R Programming Course |
R Training in Chennai

soumya said...

This is an excellent blog thanks for sharing valuable information with us please check it once at MSBI Online Training

Unknown said...

Thanks for the informational and unique blog.
Digital Marketing Training Institute in Chennai | SEO Training in Chennai

Unknown said...

Thanks for sharing wonderful information which helps all auidance

devOps Training | devOps supports

nilashri said...

Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!

Data Science Training in Chennai

rmouniak said...
This comment has been removed by the author.
Mirnalini Sathya said...


It is a wonderful data you offered to us I really enjoy by reading your article.
German Classes in Chennai
German Language Classes in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
JAVA Course in Chennai

sathyaramesh said...

Brilliant blog!! I just love the content which you wrote in this post. It explained the stuff very well in a neat manner.
Keep posting!!
Hadoop Training in Chennai
Hadoop Course in Chennai
Web Designing Course in Chennai
PHP Training in Chennai
Big Data Training in Chennai
Hadoop Training in Velachery
Hadoop Training in Annanagar

priya rajesh said...

Excellent blog with unique content, thanks a lot for sharing this. I love to learn more about this topic.
Data Science Training in Chennai
Data Science Certification in Chennai
R Programming Training in Chennai
R analytics Training in Chennai
Machine Learning Training in Chennai
Machine Learning course
Data Science Training in Velachery
Data Science Course in Chennai

Techdatasolutionsblog said...


Very Good Information...

SAS Training in Pune

Thank You Very Much For Sharing These Nice Tips..

high technologies solutions said...

Thanks a very sharing informative information
advanced sas training in delhi
advanced sas training in noida

subha said...

Nice blog..! I really loved reading through this article. this may help me lot guys.
C and C++ Training Institute in chennai | C and C++ Training Institute in anna nagar | C and C++ Training Institute in omr | C and C++ Training Institute in porur | C and C++ Training Institute in tambaram | C and C++ Training Institute in velachery

aarthi said...

Such a thoughtful article.Keep blogging. Java training in Chennai | Certification | Online Training Course | Java training in Bangalore | Certification | Online Training Course | Java training in Hyderabad | Certification | Online Training Course | Java Training in Coimbatore | Certification | Online Training Course | Java Training in Online | Certification | Online Training Course

Radley Co Tad said...

Great Article
big data projects for cse final year students


Java Training in Chennai



Final Year Projects for CSE



Java Training in Chennai

James said...

Science offering self-paced, self-learning science courses specially created for distance (flexible) learners. data science course in india

Pathway for German Language said...
This comment has been removed by the author.
arshiya said...

Great idea! Kind sharing this article. I got some useful information from your blog.
learn java from basics
cross platform mobile app development
top social media campaigns
what is microsoft azure
tableau interview questions for freshers
cyber security analyst interview questions

vé máy bay từ canada về Việt Nam said...

Mua vé máy bay tại Aivivu, tham khảo

các chuyến bay từ trung quốc đến việt nam

vé máy bay huế tphcm

vé máy bay từ tphcm đi hà nội

vé máy bay huế đà lạt

có vé máy bay từ mỹ về việt nam chưa

high technologies solutions said...

Thanks for uploading this post its really kind of content that i looking for very helpful post by the away. If anyone looking for best Software training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/software-testing-training-courses

high technologies solutions said...

Thanks for sharing this valuable post its very informative apart from that if anyone looking for e accounting institute in delhi so Contact Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/Courses/Tally/e-accounting-training-course

high technologies solutions said...

Your post is really good thanks for sharing this kind of content i hope you will share these kinds of stuff in near future apart from that if anyone is looking for a training institute for C++ training.Contact Here-+91-9311002620 Or Visit post Website-
https://www.htsindia.com/Courses/modular-courses/c-plus-plus-training-course

high technologies solutions said...

A big Thank you for sharing this post your content is really good by the way If anyone look for Ms Office training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/microsoft-courses/ms-office-course

high technologies solutions said...

A big thank you for sharing this post but if anyone looking for best AutoCAD training institute in delhi look here Contact Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/AutoCAD-training-courses

high technologies solutions said...

A very big thank you for sharing this post apart from that if anyone looking for best Advanced Excel training institute in delhi so contact here +91-9311002620 visit https://www.htsindia.com/Courses/business-analytics/adv-excel-training-course

high technologies solutions said...

Thanks for sharing this amazing post this is the content i really looking for, its very helpful i hope you will continue your blogging anyway if anyone looking for C++ training institute in delhi contact us +91-9311002620 visit-https://www.htsindia.com/Courses/modular-courses/c-plus-plus-training-course

Techi Top said...

thanks for sharing this information.
techitop
oreo tv for pc
tamilyogi unblock
hdsector
jalshamoviezhd
todaypk

Nam said...

có vé máy bay từ mỹ về việt nam chưa

high technologies solutions said...

Thanks for sharing this kind of post its very helpful for me, hey if anyone looking for best ms office or advance excel training institute in delhi so join with us for further more details Contact Here-+91-9311002620 Or Visit Website- https://htsindia.com/Courses/business-analytics/adv-excel-training-course

Unknown said...

I am very enjoyed for this blog. Its an informative topic. It help me very much to solve some problems. Its opportunity are so fantastic and working style so speedy. data scientist course in surat

Links For You said...

Rekordbox DJ Crack is a benefits system. This program's style for a DJs studio room. It really is free as well as a new device. RekordBox License Key

The Blogger Worlds said...

Happy Friday Quotes | Good Morning Friday Quotes. #1. Good morning with joy! I hope you have a blessed Friday and make the most of every moment. Friday Quotes Of The Day