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

52 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

Mary said...

You truly did more than visitors’ expectations. Thank you for rendering these helpful, trusted, edifying and also cool thoughts on the topic.
Android Project Center in Chennai | Android Project Center in Velachery

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

viswanath said...

AWS Training in Bangalore - Live Online & Classroom
myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


IOT Training in Bangalore - Live Online & Classroom
IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

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

Pawel Co Faddey said...

Great Article
R Project Topics for Computer Science
FInal Year Project Centers in Chennai

JavaScript Training in Chennai
JavaScript Training in Chennai

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

cynthiawilliams said...

Truly a very good article on future technology. Thanks for discussing this, I love to learn more about this topic.
DevOps Training in Chennai
DevOps course in Chennai
AWS Training in Chennai
AWS Training in Velachery
Cloud Computing Courses in Chennai
RPA course in Chennai
DevOps Training in OMR
DevOps Training in Chennai

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

lekha mathan said...

Very interesting post! Thanks for sharing your experience suggestions.
Aviation Courses in Chennai
air hostess academy in Chennai
Airport Management Training in Chennai
airport ground staff training in Chennai
Aviation Academy in Chennai
air hostess training in Chennai
airport management courses in Chennai
ground staff training in Chennai

Vicky Ram said...

Enjoyed your approach to explaining how it works, hope to see more blog posts from you. thank you!

yellowlocaldirectory
Education

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

kavithasathish said...

Thanks for this innovative blog. Keep posting the updates.
pearson vue
German Language Classes in Chennai
IELTS Training in Chennai
Japanese Language Course in Chennai
spanish classes in chennai
Best Spoken English Classes in Chennai
Spoken English Classes in Tambaram
Spoken English Classes in Anna Nagar

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

shakeel1995 said...

Awesome collection of the content thanks for sharing
salesforce advanced administrator training
best devops training in hyderabad
java full stack training

offpageseo70 said...

Nice blog,I understood the topic very clearly,And want to study more like this.
Data Scientist Course

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

Priyanka said...

Attend The Data Science Courses From ExcelR. Practical Data Science Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses.
Data Science Courses

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

Priyanka said...

Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
Data Analyst Course

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

high technologies solutions said...

Big thank you for sharing this post its very knowledgeable and very helpful i hope that you will continue to post these kinds of contents in future 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

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