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

48 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...

Interesting post! This is really helpful for me. I like it! Thanks for sharing! Java Training in Chennai

Unknown said...

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

Anonymous said...

Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities.With companies across industries striving to bring their research and analysis (R&A) departments up to speed, the demand for qualified data scientists is rising.
data science training in bangalore

Python is a high-level, interpreted, interactive and object-oriented scripting language. Python is designed to be highly readable. It uses English keywords frequently where as other languages use punctuation, and it h
as fewer syntactical constructions than other languages.python interview questions and answers

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

Hemapriya said...

This blog is very much helpful to us. Thanks for your information.
SAS Training Chennai
SAS Training Institute in Chennai
SAS Courses in Chennai
SAS Training Center in Chennai
SAS Analytics Training in Chennai

rmouniak said...
This comment has been removed by the author.
Dharani M said...

Nice blog
sas training in Marathahalli

sas training institutes in Marathahalli

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

Kayal said...

I recently saw your post! I was very happy to visit your blog. I like so much and I hope more valuable post from your post...

Unix Training in Chennai
Unix shell scripting Training in Chennai
Excel Training in Chennai
Corporate Training in Chennai
Embedded System Course Chennai
Linux Training in Chennai
Tableau Training in Chennai
Unix Training in Chennai
Unix shell scripting Training in Chennai

yuvanthi said...

I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision.keep blogging.
Regards,
PHP Training in Chennai
PHP Course in Chennai
Tally Course in Chennai
ReactJS Training in Chennai
microsoft dynamics crm training in chennai
PHP Training in Chennai
PHP Course in Chennai

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

for ict 99 said...

Great Article
final year projects on machine learning

Final Year Project Centers in Chennai


JavaScript Training in Chennai
JavaScript Training 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

priyash said...

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression
data science interview questions

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

Data Science Course in Bhilai - 360DigiTMG said...

Fantastic blog extremely good well enjoyed with the incredible informative content which surely activates the learners to gain the enough knowledge. Which in turn makes the readers to explore themselves and involve deeply in to the subject. Wish you to dispatch the similar content successively in future as well.

data science training in bhilai

Data Science Course in Bhilai - 360DigiTMG said...

Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.

Data Science Course in Bhilai

Dreamsoft Consultants said...

Your post is really good thanks for sharing these kind of post but if anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in Noida, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-Noida.html

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

high technologies solutions said...

big thank you for sharing this content If anyone looking for best Sas training institute in Delhi Contact Here-+91-9311002620 Or Visit our website https://www.htsindia.com/Courses/business-analytics/sas-training-institute-in-delhi

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

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
Continue posting. A debt of gratitude is in order for sharing.
data science course in kolhapur

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

haseeb said...

Kaspersky Total Security 2019 license key to go to the right page. The internet is not only one place to enjoy everyday, but also dangerous because it is . https://cyberspc.com/kaspersky-total-security-crack/

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

Mudassara Shahzad said...

Greetings! This page has a lot of useful information on it!
TunesKit Apple Music Converter VST