topic Reading Sas datasets in PowerBI in Microsoft Integration with SAS

Reading Sas datasets in PowerBI

MartinLomborg1 — Thu, 15 Apr 2021 10:31:38 GMT

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)

Re: Reading Sas datasets in PowerBI

LinusH — Tue, 27 Apr 2021 15:53:20 GMT

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...

Re: Reading Sas datasets in PowerBI

McDiddles — Tue, 13 Jul 2021 09:49:24 GMT

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

Re: Reading Sas datasets in PowerBI

nar_sas — Fri, 29 Jul 2022 20:18:57 GMT

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-sources-accessible-through-ole-db

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

Re: Reading Sas datasets in PowerBI

totoleheros — Wed, 20 Oct 2021 12:01:09 GMT

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