Reading SAS Data on my Windows laptop in Microsoft Power BI

[bnawrocki]

Like some others here, I am trying to use Power BI Desktop to connect to some SAS files that I have stored on my local laptop.

My laptop is running Windows 10. On it, I have SAS 9.4 M7 and Power BI Desktop installed.

Following some suggestions, I had luck connecting Power BI to SAS by choosing:

Get Data --> Other --> OLE DB

And using this Connection String:  provider=sas.IOMProvider.9.47

(First time i do this, it asks me for my userid and password - I just entered my Windows userid and password for my laptop)

And it works (sort of)! I can see a list of SAS built-in libraries and datasets - MAPS and SASHELP libraries.

How do I add additional libraries to this list? For example, I'd like to see the SAS datasets stored on "C:\mySASFiles" which I usually submit a SAS statement in SAS Enterprise Guide like this: 

LIBNAME MYFILES "C:\mySASFiles";

[MrSantos · Accepted solution]

Hello,

Probably you'll need to pre-assign the library by declaring it on either the default autoexec or sasv9.cfg files. This way when PowerBi connects and kick-starts the SAS session the library should be available.

You can include the LIBNAME statement in an autoexec file, which is executed automatically when you start your SAS session.

/* autoexec.sas */
libname mylib 'C:\path\to\your\library';

You can also add the LIBNAME statement to your SAS configuration file (SASV9.CFG).

-LIBNAME mylib 'C:\path\to\your\library'

[bnawrocki]

Thanks that worked!

IT has restricted my access to SASV9.CFG file, so I created an AUTOEXEC.SAS file containing the LIBNAME statements in a different folder:

C:\Users\my-UserID.

Then, I had to go to Windows Settings, Environment Variables, and add that folder to my PATH, so SAS would search for AUTOEXEC.SAS there.

[GBL__]

Would you be able to expand a little more on your connection string?

I understand the Provider= portion, but surely there are more options need for the full connection string?

[bnawrocki]

You're right. The full connection string contains a lot more options, like this example:

Provider=SAOLEDB.1; Data Source=your_server_name; Initial Catalog=your_database_name; User ID=your_username; Password=your_password;

But I'm connecting to SAS 9.4 running local on my Windows PC, so the only REQUIRED option is "Provider=".

The other options are needed when you are connecting to SAS Viya or SAS 9.4 on a remote server.

I have to admit, the Power BI OLE DB connection tool did ask for my userid and password the first time I tried the connection - so I entered my Windows domain ID and password into some textboxes that popped up, but after that, it didn't ask for userid or password again.