Help using Base SAS procedures

Writing dozens of tables into speadsheets of excel

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Writing dozens of tables into speadsheets of excel

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";
;



Accepted Solutions
Solution
‎09-29-2014 06:22 PM
PROC Star
Posts: 7,356

Re: Writing dozens of tables into speadsheets of excel

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

View solution in original post


All Replies
Super User
Posts: 17,734

Re: Writing dozens of tables into speadsheets of excel

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

PROC Star
Posts: 7,356

Re: Writing dozens of tables into speadsheets of excel

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.

Super User
Posts: 3,100

Re: Writing dozens of tables into speadsheets of excel

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;

Solution
‎09-29-2014 06:22 PM
PROC Star
Posts: 7,356

Re: Writing dozens of tables into speadsheets of excel

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

Super User
Super User
Posts: 7,392

Re: Writing dozens of tables into speadsheets of excel

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;

Super User
Posts: 17,734

Re: Writing dozens of tables into speadsheets of excel

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 367 views
  • 7 likes
  • 5 in conversation