Reading a spde file into power BI

[alepage]

Hello,

I have install the R software on my computer in order to be able to read / import a sas dataset in Power BI, for validation purpose.  I have read a little bit the documentation on that, but it refers to sas7bdat.  

How can we read a spde file in Power BI without reconverting it to the base format ?

[AlanC]

I am out on a limb here since it has been years since I worked with SPDE. The sas7bdat is a binary proprietary format. SPDE files should be in that same vein. However, the sas7bdat and spde are both ODBC compliant. Download the ODBC drivers and use them vs using the binary readers.

For simple SAS data reading, use ODBC and OleDB where possible. They are not perfect but they should give you what you need. This also keeps you from needing R to read a SAS binary protocol.

[Kurt_Bremser]

.spde files come from SAS libraries using the Scalable Performance Data Engine. You usually have more than one .spde file for a single dataset, as SPDE is designed to work with several discrete "buckets" on discrete hardware, so the I/O load is spread across devices. To successfully use them, you would also need to recreate the structure of the original SPDE library (where is the primary directory, which bucket goes to which secondary directory, ...)

So it might very well be that your file is useless outside of the original SAS installation.

[SASKiwi]

There are many ways to load data into Power BI. One way we use a lot is to load our SAS tables into SQL Server from where Power BI can read the tables directly. That's a lot easier than using CSVs or similar. I concur with @Kurt_Bremser that you will have to convert SPDE to a normal SAS library if you want to continue with the R approach.

[alepage]

I have made some test , I have able to read / import the sashelp.class into Power BI  using the R script below

library(sas7bdat)

require(sas7bdat)

Class1 <-read.sas7bdat("C:/Temp/PowerBiTest/class.sas7bdat")

When the dataset is on our unix server (see the R script below, the three dots are for safety purpose), I receive an error unable to open the file.

library(sas7bdat)

require(sas7bdat)

Class1 <-read.sas7bdat("/finsys/.../datasets/class.sas7bdat")

Could you please provide me an example how to load a sas dataset using SQL server connection?

Regards,

[SASKiwi]

PROC DATASETS is one of the easiest ways:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"
 qualifier = "MyDatabase" schema = "MySchema" insertbuff = 10000;

proc datasets library = sqlsrvr nolist;
  append base = MySQLTable data = MySASLib.MySASTable;
run;
quit;

This appends rows from the SAS dataset MYSASTABLE to the SQL Server table MYSQLTABLE. If it doesn't exist, it will be created, assuming you have the SQL permissions to do so.

[alepage]

Do I need to put that script into Power BI or I convert my sas database as an oracle table on a specific server then I connect to that sql server in power bi to obtain my table ?

[SASKiwi]

What SAS/ACCESS products do you have? I assumed you had ODBC which will work with any ODBC-compliant database including SQL Server. Run PROC SETINIT to confirm:

proc setinit;
run;

That script is a SAS program and you run it from SAS to load a SAS table into an SQL Server database.

[alepage]

Here's the logfile content from the execution of proc setinit;run;

Original site validation data
Current version: 9.04.01M7P080520

CPU A: Model name='' model number='' serial='+4'.
Expiration: 29JUN2023.
Grace Period: 15 days (ending 14JUL2023).
Warning Period: 18 days (ending 01AUG2023).
System birthday: 12MAY2022.
Operating System: LIN X64 .
Product expiration dates:
---Base SAS Software 29JUN2023 (CPU A)
---SAS/STAT 29JUN2023 (CPU A)
---SAS/GRAPH 29JUN2023 (CPU A)
---SAS Integration Technologies 29JUN2023 (CPU A)
---SAS/Secure 168-bit 29JUN2023 (CPU A)
---SAS Enterprise Guide 29JUN2023 (CPU A)
---SAS/ACCESS Interface to Oracle 29JUN2023 (CPU A)
---SAS/ACCESS Interface to PC Files 29JUN2023 (CPU A)
---SAS/ACCESS Interface to ODBC 29JUN2023 (CPU A)
---SAS Workspace Server for Local Access 29JUN2023 (CPU A)
---High Performance Suite 29JUN2023 (CPU A)
---SAS Add-in for Microsoft Excel 29JUN2023 (CPU A)
2 The SAS System 10:18 Monday, June 20, 2022

---SAS Add-in for Microsoft Outlook 29JUN2023 (CPU A)
---SAS Add-in for Microsoft PowerPoint 29JUN2023 (CPU A)
---SAS Add-in for Microsoft Word

[SASKiwi]

SAS/ACCESS Interface to ODBC is licensed so you can use that to load SAS tables into SQL Server as I have already explained. You will obviously need to change the LIBNAME statement to point to your required SQL Server database. If you haven't done this before, I suggest consulting your SAS administrator.

[AlanC]

One thing to keep in mind, SAS/Access to ODBC is a push operation from SAS and requires SAS to run. You can also use a pull operation using the SAS ODBC driver, which is free, and pull the data direct into PowerBI.

[LinusH]

Even though the ODBC driver is free, you still need a SAS server to respond, either a local SAS installation, or a SAS/SHARE server (with the additional SAS/SHARE*NET license).

[AlanC]

[Edited to removed ODBC-AC] SAS datasets can be read 100% using ODBC/OleDb without a SAS server using the drivers from SAS. There are 3 providers. Use local provider not IOM or Share. Use it all of the time at client sites.

[LinusH]

I was aware you could do it with OLEDB, but not ODBC. The documentation still states you need additional licensing, but if you have a link or similar to share it would be great!
https://documentation.sas.com/doc/en/odbcdref/9.4/p1esd8t7nrdvmfn1guh905g1dnvk.htm

[alepage]

Could you please provide a link to the web page related to OLEDB sas documentation