Holger Schimanski on 02 Mar 2023 11:10:37
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.
- Comments (1)
RE: Read SAS files in the sas7bdat data format
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_TableWithColumnDescriptions
To 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.