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):
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:
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):
![]() |
Error Message Text of error: "The 'SAS Local Data Provider 9.44' provider is not registered on the local machine." |
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:
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 |
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
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:
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
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|
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
This is an excellent blog thanks for sharing valuable information with us please check it once at MSBI Online Training
Interesting post! This is really helpful for me. I like it! Thanks for sharing! Java Training in Chennai
Thanks for the informational and unique blog.
Digital Marketing Training Institute in Chennai | SEO Training in Chennai
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
Thanks for sharing wonderful information which helps all auidance
devOps Training | devOps supports
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
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
Nice blog
sas training in Marathahalli
sas training institutes in Marathahalli
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
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
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
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
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
Great Article
final year projects on machine learning
Final Year Project Centers in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Very Good Information...
SAS Training in Pune
Thank You Very Much For Sharing These Nice Tips..
Thanks a very sharing informative information
advanced sas training in delhi
advanced sas training in noida
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
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
Great Article
big data projects for cse final year students
Java Training in Chennai
Final Year Projects for CSE
Java Training in Chennai
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
Science offering self-paced, self-learning science courses specially created for distance (flexible) learners. data science course in india
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
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
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
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
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
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
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
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
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
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
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
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
thanks for sharing this information.
techitop
oreo tv for pc
tamilyogi unblock
hdsector
jalshamoviezhd
todaypk
có vé máy bay từ mỹ về việt nam chưa
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
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
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
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
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
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/
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
Greetings! This page has a lot of useful information on it!
TunesKit Apple Music Converter VST
Post a Comment