How to connect sas with powerbi??How to read sas file data in powerbi
Usually, third party vendor SW cannot read SAS files directly, tablau being an exception.
So you need either a SAS user export the data to format convenient for you, like CSV?
If you need a more robust and constant solution, you can read SAS data via ODBC, which requires a local SAS installation, or a SAS Server running SAS/SHARE or SAS SPD Server.
Two-fold response:
To help us to help you, please tell us more about your SAS installation - what products you have installed, etc?
Usually, third party vendor SW cannot read SAS files directly, tablau being an exception.
So you need either a SAS user export the data to format convenient for you, like CSV?
If you need a more robust and constant solution, you can read SAS data via ODBC, which requires a local SAS installation, or a SAS Server running SAS/SHARE or SAS SPD Server.
Actullay i retrived the data of sas file using R script,can u tel me how to read sas using odbc
It assumes you have "SAS/Access to ODBC" licensed, and depends on how you have ODBC configured, but if both are there, then it should be as simple as:
libname MyLib odbc dsn="ODBC settings";
.. give or take a few additional parameters (username, password).
Suggest you read:
I tried going through ODBC route , but no success . Not able to view the data in Power BI .
Can you please explain ..
Also I don't have any parameter values for the User Name and passwored for SAS datasets .. from where I will be able to fetch that ?
Hi LinusH,
I am not a SAS expert.
Our business has a SAS share and I want to connect to SAS share read the data sets via ODBC Connection. I am able to do this in Excel but not in Power BI. Is there a way we can do this or I need to install SAS. I have tried with colleagues that have SAS installed but it does NOT work.
Regards
Raju
I am also eagerly waiting for them to build in a connection to SAS files or direct to SAS server.
I use R-scripts to connect as in our environment at work, I cannot create ODBC connections to the files.
1. Install R
2. Get the sas7bdat library and install that into R
3. in Power BI, connect to data with an R script and use the following script (remember that R uses linux convention of forward slashes, not back slashes):
library(sas7bdat)
require(sas7bdat)
input<-read.sas7bdat("Z:/network/project/mysasfile.sas7bdat")
Hi ,
Is there any solution which we can read sas data set into power bi.
Regards
Sudheer
Hi
so, yes, you can. Its a little bit of a process to set up. however it seems to work well.
1. Download and install R - the later the version the better. I have 3.4.3.
2. download and install the sas7bdat library for R.
This can be done through R, by using the command install.packages("sas7bdat")
This will download the files to some downloads folder. R will tell you where.
I am no R wizard, so the easiest, I have found, is to then browse to the location. Under the data folder, there is a file 'sas7bdat.sources.RData'. Drag and drop this file into R, and it will install it.
You can close R at this point.
3. Open PoweBI. Under settings, check that it picked up your R installation.
if not, you will need to point it to the right folder.
4. click 'get data' and choose the 'R script' option under other.
5. the script you'll us is:
library(sas7bdat)
require(sas7bdat)
input<-read.sas7bdat("Z:/folder/folder/my_SAS_file.sas7bdat")
remember that R, like Linux, uses '/' in its file paths, not '\' as in Windows.
6. This script will pull in your data and you'll be able to refresh it as normal.
Good Luck!
GD
Hi,
Thank you for the solution. I haven't tried this at my end as I don't have the R installed in the system (Have requested the admin). Meanwhile, I want to know if you have come across the conflict in date conversion between SAS and PowerBI. I had this issue (in 2016) when I tried to use SAS dataset on Excel due to different beginning dates they have (1900, 1960), and the solution then was to convert the SAS date into characters (Not a great solution). Do you know a solution for this? or has this been overcome in PowerBI?
Thankfully,
Rohan
Hi Rohan
Yes. I know of this problem, I used to always use the character conversion as well. However fairly recently I had to start using actual dates. Last I checked, this has not been overcome in PowerBI yet. There is great to get a proper connector between SAS and PowerBI, but I suspect there is some corporate disagreement or something. This problem is also prevalent if you connect Excel directly to the SAS files. I can't remember if you use SAS to store data on a SQL server Db and then connect Excel/PowerBi to that, if this problem also exists - its been a while since I could do that, so I can't recall.
So what I do is one of two things,
1. After you final data step (or what ever it may be) convert all you date column to excel by subtracting 21916 (that is the number of days difference in their reference date).
e.g. Loan_application_date = Loan_application_date - 21916;
2. Create a new date variable.
e.g. Loan_application_date_Excel = Loan_application_date -21916;
However, I do believe that if you export your files to an excel file, it does the conversion for you - but I am not sure off the top of my head. Also, if you use the SAS plugin, then it also does the conversion for you. I however don't use the plugin to directly connect to data as it does not link in/hookup to the 'refresh all' function of Excel.
Should you want to make the time conversion - as SAS works in seconds after midnight, while Excel works in fractions of a day. This can be achieved in a similar means:
1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).
e.g. Loan_application_time = Loan_application_time / 86400;
2. Create a new date variable.
e.g. Loan_application_time_Excel = Loan_application_time / 86400;
Should you want to make the datetime conversion.
This can be achieved in a similar means:
1. After you final data step (or what ever it may be) convert all you date column to excel by dividing 86400 (that is the number of second in a day).
e.g. Loan_application_time = (Loan_application_time / 86400) - 21916;
2. Create a new date variable.
e.g. Loan_application_time_Excel = (Loan_application_time / 86400) - 21916;
then obviously, you can do the reverse as well if you need to take Excel date/time/datetime to SAS.
Also, FYI SAS & SQL have the same reference date and time units.
Hope this helps
~GD
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!