topic Re: How to export SAS data to LibreOffice Calc (an open source Excel) in SAS Programming

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

cashaowan — Sat, 12 Oct 2024 23:10:09 GMT

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?

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

Patrick — Sun, 13 Oct 2024 01:07:36 GMT

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.

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

cashaowan — Mon, 14 Oct 2024 02:36:07 GMT

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.

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

Patrick — Mon, 14 Oct 2024 06:58:34 GMT

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

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

cashaowan — Mon, 14 Oct 2024 14:02:12 GMT

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.

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

Cynthia_sas — Mon, 14 Oct 2024 14:10:03 GMT

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

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

Tom — Mon, 14 Oct 2024 15:16:47 GMT

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

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

Kurt_Bremser — Mon, 14 Oct 2024 17:04:05 GMT

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