Writing dozens of tables into speadsheets of excel

[wutao9999]

I am using the below procedures to write datasets into multiple spreadsheets into the same excel file.  As there are more than 2 dozens of datasets needed to be exported to excel, the below procedure is kind of slow because the excel file needs to reopen every time when a dataset is exported.

Is there a way that the excel is opened only one time for exporting all of the 2 dozens of datasets into the excel?

EXPORT DATA=data1

OUTFILE="C:\output.xlsx"

DBMS=EXCELCS REPLACE;

SHEET="sheet1";
;

EXPORT DATA=data2

OUTFILE="C:\output.xlsx"

DBMS=EXCELCS REPLACE;

SHEET="sheet2";
;

[Reeza]

You could use the libname method:

libname out pcfiles path='path to .xlsx';

data out.data1;

set data1;

data out.data2;

set data2;

etc.

libname out;

You could also look into the custom code built by

http://support.sas.com/resources/papers/proceedings14/1793-2014.pdf

[art297]

I would say that the libname method suggested by will be your quickest method. If for some reason you want to try the other method she suggested, use the most recent version of the macro (i.e., A Poor/Rich SAS Users Proc Export - sasCommunity ).

However, the macro would have to be run separately for each dataset and would open, save and close Excel each time. I`d only recommend it, in this case, if you needed more control over formats, labels and ranges.

[SASKiwi]

If you are going to try the LIBNAME approach then you can also use PROC DATASETS to copy SAS tables as tabs into an Excel workbook.

libname MyExcel excel "MyWorkBook.xls";

proc datasets library = MyExcel nolist;

   copy in = MySAS out = MyExcel;

  select table1 table2 table3;

   run;

quit;

[art297 · Accepted solution]

I may have to revise my suggestion, but would first like to see the results of the follow three tests on large (in Excel terms) file, namely three sheets with each having 950,000 rows:

The code I used was:

data table1 (drop=i);

  set sashelp.class;

  do i=1 to 50000;

    output;

  end;

run;

data table2;

set table1;

run;

data table3;

set table1;

run;

libname MyExcel1 excel "c:\MyWorkBook1.xlsx";

proc datasets library = MyExcel1 nolist;

   copy in = work out = MyExcel1;

  select table1 table2 table3;

run;

libname MyExcel2 excel "c:\MyWorkBook2.xlsx";

data MyExcel2.table1;

  set table1;

run;

data MyExcel2.table2;

  set table2;

run;

data MyExcel2.table3;

  set table3;

run;

%exportxl(data=table1,outfile=c:\MyWorkBook3.xlsx,type=N)

%exportxl(data=table2,outfile=c:\MyWorkBook3.xlsx,type=A)

%exportxl(data=table3,outfile=c:\MyWorkBook3.xlsx,type=A)

The performance numbers I`m interested in are:

(1) how long each of the three processes take

(2) the sizes of the resulting 3 xlsx files and

(3) how long it takes to open each of the three workbooks

and, of course, which version of SAS you were running

[RW9]

I always seem to have problems with the libnames, they get stuck.  With the proc datasets the connection to the xlsx does not want to terminate.

Has anyone tried the 9.4 zip filename as yet?  I only have 9.3 but would really like to know some results.  Looking at the help I was hoping for something along the lines of:

filename xcel zip 'c:\test_file.xlsx' member="xl\workbook.xml";
data _null_;
length buffer $256;
infile xcel;
file xcel(xl\workbook.xml);
input buffer $varying256.;
if buffer="<sheets>" then do;
  ...
end;
if buffer="<definedNames>" then do;
  ...
end;
run;

filename xcel zip 'c:\test_file.xlsx' member="xl\worksheets\sheet1.xml";
data _null_;
set data1;
file xcel(xl\worksheets\sheet1.xml);
if _n_=1 then do;
  ... put the header data
end;
...put the row data

run;

[Reeza]

I've tried ODS Excel in 9.4 where it creates an actual excel file, but the code is similar to the ODS tagsets.excelxp and that works fine.