In context of Clinical Development in Pharma, SAS is very much used for statistical analysis and preparation of datasets for submission. The submission datasets with the final results of a clinical trial are created as SAS files in the sas7bdat data format. It would be extremely helpful, if you could directly open these sas7bdat files from Power BI.
The documented way to access Power BI to SAS data is via SAS server and "database" access, but this is quite painful to setup and requires a lot of components configured and infrastructure to run. If you are a geek, you probably find out, that you can open sas7bdat files via LocalProvider e.g. OLE DB connection string like "Provider=SAS.LocalProvider.1; Data Source=c:\temp\MySasData" to the folder of your sas7bdat files. But this is not really user friendly.
Tableau and Spotfire are able to open and read in SAS sas7bdat files same like CSV or Excel files in the Get File Data feature. Spotfire is using also the SAS OLE DB driver same like suggested for Power BI, but lets you select a file rather that asking for OLE DB configuration string.
Would be awesome to simplify it for the end users.
3 Comments
This is very common when working with SAS datasets for reports and visualization to show the label resp. description rather than the technical column name .
In Power BI I have to use Table.Schema, get the description of the columns from there and then Table.renameColumns using this schema information.
let Source = OleDb.DataSource("provider=sas.LocalProvider.9.47;data source=c:\temp\MySasData"), SAS_Table = Source{[Name="MySASTable",Kind="Table"]}[Data], SAS_Schema = Table.Schema(SAS_Table), SAS_ColumnDescriptions = Table.SelectColumns(SAS_Schema,{"Name", "Description"}), SAS_TableWithColumnDescriptions = Table.RenameColumns(SAS_Table, Table.ToRows(SAS_ColumnDescriptions)) in SAS_TableWithColumnDescriptionsTo achieve the same Spotfire just use checkbox "Use Description as column name (if available)".
Would be great to have this also easy accessible in Power BI.
- Status added:New
Directly opening sas7bdat files in Power BI has always been painful, Cepheus Portus solves this cleanly: It connects Power BI directly to SAS data, read‑only, air‑gapped, with no copies created anywhere. You get immediate access to SAS datasets inside Power BI, plus a GxP validation dossier ready for Quality sign‑off on day one. In short: no exports, no infrastructure headaches, just compliant, auditable connectivity between SAS and Power BI. Reference: Cepheusonline
For more Information mail to: [email protected]
