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";
;
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
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
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.
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;
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
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: