Reading Sas datasets in PowerBI

[MartinLomborg1]

Hi,

Please help!

I am looking for a way to read and load Sas datasets located on a local PC drive directly from MS PowerBI .

The solution cannot be open source like R

Systems
Operating system is Windows 10 Pro
PowerBI desktop is Version: 2.91.884.0 64-bit (marts 2021)

What I have tried:
• ODBC, installing Sas driver 9.47
• Installing Sas Foundation 9.4 TS level 1M7
• Setting up an ODBC data source system DSN 64 bit (32 bit are not shown in PowerBI connection)
• In ODBC driver configuration I use the default options and have tried naming the server __5001 (example in documentation). Also tried __8621 and __9621. Things work in Excel when I name it like that.
• In library in chose local drive C:\sasdata to keep it simple

It works in Excel using Power Query 

Result when connecting via ODBC in PowerBI

Sas base 9.4 opens

ODBC connection shows in Navigator

Error messages

I varies a little bit actually, but looks mainly like this:

(please see some screen shots that I get)

-----------------------------------------------------------------------------------------------------

Unexpected error: Container exited unexpectedly with code 0xC0000005. PID: 11044.
Used features: Odbc.DataSource/SASDRV32.DLL/09.04.0000/SAS/9.04.01M7P080520.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Container exited unexpectedly with code 0xC0000005. PID: 11044.
Used features: Odbc.DataSource/SASDRV32.DLL/09.04.0000/SAS/9.04.01M7P080520. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Container exited unexpectedly with code 0xC0000005. PID: 11044.
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()
at Microsoft.Mashup.Evaluator.ChannelMessenger.Read(MessageChannel channel)
at Microsoft.Mashup.Evaluator.ChannelMessenger.MessageChannel.Read()
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

[LinusH]

If I understand it correctly, your SAS DSN works for Excel?

Have you trid PowerBI with other local data sources?

This might be an issue with PowerBI, not SAS...

[McDiddles]

I am having a similar issues. PowerBi can connect to other sources, just SAS that is failing.

[nar_sas · Accepted solution]

You should be able to connect to SAS from PowerBI using OLE DB.  Microsoft provides documentation on how to do this on their website here: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-using-generic-interfaces#data... 

which uses the SAS Providers for OLE DB from the SAS Download site here: https://support.sas.com/downloads/package.htm?pid=648 

I just tried this in my PowerBI installation on my desktop and it seemed to be working for me.  I would expect that if you have something tricky like custom formats it might not apply them correctly, but this might be worth a try.  

Editor's Note:
I am including totoleheros link to the latest versions of SAS Providers for OLEDB: https://support.sas.com/downloads/browse.htm?fil=&cat=64

[totoleheros]

The latest versions of SAS Providers for OLEDB (94M7) can be found here : https://support.sas.com/downloads/browse.htm?fil=&cat=64