How to export SAS data to LibreOffice Calc (an open source Excel)

[cashaowan]

I need to export a large SAS dataset to LibreOffice Calc, which is open source Excel similar to MicroOffice Excel. This is due to server restrictions where MicroOffice is not installed. 

I used the following codes:

proc export data=mydata

outfile=''directory\mydata.ods' dbms=ODF replace;

run;

The log file says "DBMS  type ODF not valid for expert". Any solutions? If MicroSoft Office is not allowed, how to take this dataset out? 

[Patrick]

You don't need MS Office installed on the server to create MS Excel files using SAS. You do need SAS Module SAS Access Interface to PC files installed and licensed. 

To check if SAS Access Interface to PC Files is licensed and installed run below commands and then check in the SAS log if it appears.

/* licensed modules */
proc setinit;
run;

/* installed modules */
proc product_status;
run;

To further support you please let us first know if SAS Access Interface to PC Files is available to you.

Btw: What is a "large SAS dataset"? Excel got a limit of 1M rows per sheet.

[cashaowan]

I run the codes you provided. SAS/Access Interface to PC Files is installed. The dataset I created has less than 5000 observations. Would appreciate if you can suggest the next SAS procedure. 

[Patrick]

---

@cashaowan wrote:

I run the codes you provided. SAS/Access Interface to PC Files is installed. The dataset I created has less than 5000 observations. Would appreciate if you can suggest the next SAS procedure. 

---

Below one option:

proc export 
  data=sashelp.class
  outfile='c:\temp\myExcel.xlsx' 
  dbms=xlsx 
  replace
  ;
  sheet='Class';
run;

And here some more options:

https://blogs.sas.com/content/sasdummy/2012/01/25/export-to-excel-got-easier/

https://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

Note: The path to the Excel file must be accessible from the location where the SAS process executes. If this is a SAS Server under Linux then it would be some Linux path.

[cashaowan]

I think it is the computer station itself that isn't allowed to have Microsoft windows installed. That's why I need to consider LibreOffice Calc. I used the option "dbms=ODF" instead of "dbms=xlsx" in my initial codes. 

[Cynthia_sas · Accepted solution]

Hi:

  If LibreOffice can open a comma separated value file (.CSV), then you could use that with PROC EXPORT or you could use ODS CSV -- either should create a CSV file that can be imported. Here's an example using SASHELP.CLASS:

proc export data=sashelp.class
            file='c:\temp\class_ex.csv'
			dbms=csv 
			replace;
run;

ods csv file='c:\temp\class_ods.csv';
proc print data=sashelp.class noobs;
  var name sex age height weight;
run;
ods csv close;

Cynthia

[Tom]

---

@cashaowan wrote:

I think it is the computer station itself that isn't allowed to have Microsoft windows installed. That's why I need to consider LibreOffice Calc. I used the option "dbms=ODF" instead of "dbms=xlsx" in my initial codes. 

---

SAS does not provide an engine to write ODF files.

But LibreOffice should be able to read (and I assume also write) actual XLSX files.  

[Kurt_Bremser]

LibreOffice supports all MS Office formats, so you should use the XLSX engine.