I asked this as part of someone else's thread but, in the event you didn't see it, I'd be interested in knowing how the macro that FriedEgg, Tom Abernathy and I (and, unofficially Randy Herbison) wrote and presented at SGF earlier this year, compares with various SAS methods for creating multiple sheet Excel workbooks. You can download the macro at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export
I'd run the tests myself, but I no longer have access to SAS. I'm interested because a number of people have asked me if the macro is a better choice than using a libname to accomplish the task. I already know that the macro is preferable to proc export, but I don't know how to respond regarding other options.
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 (real time and cpu time)
(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. Obviously, time comparisons with any alternative approaces (e.g., ODS and Proc Export) would also be welcome.
Thanks in advance,
Art
Arthur, I understand the problem with UE now.
Reverse engineering what is done in the code. It copies all data to the clipboard. Than it is pasted into the sheet. Very fast on a local system if you have a clipboard as it is in-memory. The UE does not support the clipboard not having the DMS. I was looking why it is done this way and could not found a VB object call to import data. Import a XML could be an option,. I am not having experience with that.
Athur ,
I leave it to others. My SAS is 9.2 . Although I can run it under SAS University Version .But my laptop is low level . Not good to run a large Macro. And I am going to go to sleep now.
Xia Keshan
Arthur,
I can't run this code under SAS9.2 . let another one do it.
1 options stimer; 2 3 data table1 (drop=i); 4 set sashelp.class; 5 do i=1 to 50000; 6 output; 7 end; 8 run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.TABLE1 has 950000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.27 seconds cpu time 0.14 seconds 9 10 data table2; 11 set table1; 12 run; NOTE: There were 950000 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE2 has 950000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.24 seconds cpu time 0.25 seconds 13 14 data table3; 15 set table1; 16 run; NOTE: There were 950000 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE3 has 950000 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.25 seconds cpu time 0.24 seconds 17 18 libname MyExcel1 excel "c:\temp\MyWorkBook1.xls"; NOTE: Libref MYEXCEL1 was successfully assigned as follows: Engine: EXCEL Physical Name: c:\temp\MyWorkBook1.xls 19 20 proc datasets library = MyExcel1 nolist; 21 copy in = work out = MyExcel1; 22 select table1 table2 table3; 23 run; NOTE: Copying WORK.TABLE1 to MYEXCEL1.TABLE1 (memtype=DATA). NOTE: There were 65536 observations read from the data set WORK.TABLE1. WARNING: File deletion failed for MYEXCEL1.TABLE1.DATA. ERROR: Execute: Unable to insert row ERROR: File MYEXCEL1.TABLE1.DATA has not been saved because copy could not be completed. NOTE: Copying WORK.TABLE2 to MYEXCEL1.TABLE2 (memtype=DATA). NOTE: There were 65536 observations read from the data set WORK.TABLE2. WARNING: File deletion failed for MYEXCEL1.TABLE2.DATA. ERROR: Execute: Unable to insert row ERROR: File MYEXCEL1.TABLE2.DATA has not been saved because copy could not be completed. NOTE: Copying WORK.TABLE3 to MYEXCEL1.TABLE3 (memtype=DATA). NOTE: There were 65536 observations read from the data set WORK.TABLE3. WARNING: File deletion failed for MYEXCEL1.TABLE3.DATA. ERROR: Execute: Unable to insert row ERROR: File MYEXCEL1.TABLE3.DATA has not been saved because copy could not be completed. NOTE: Statements not processed because of errors noted above. 24 25 libname MyExcel2 excel "c:\temp\MyWorkBook2.xls"; NOTE: Libref MYEXCEL2 was successfully assigned as follows: Engine: EXCEL Physical Name: c:\temp\MyWorkBook2.xls 26 NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE DATASETS used (Total process time): real time 2.10 seconds cpu time 2.04 seconds 27 data MyExcel2.table1; 28 set table1; 29 run; ERROR: Execute: Unable to insert row NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 65536 observations read from the data set WORK.TABLE1. WARNING: The data set MYEXCEL2.table1 may be incomplete. When this step was stopped there were 65535 observations and 5 variables. ERROR: ROLLBACK issued due to errors for data set MYEXCEL2.table1.DATA. NOTE: DATA statement used (Total process time): real time 0.67 seconds cpu time 0.68 seconds 30 31 data MyExcel2.table2; 32 set table2; 33 run; ERROR: Execute: Unable to insert row NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 65536 observations read from the data set WORK.TABLE2. WARNING: The data set MYEXCEL2.table2 may be incomplete. When this step was stopped there were 65535 observations and 5 variables. ERROR: ROLLBACK issued due to errors for data set MYEXCEL2.table2.DATA. NOTE: DATA statement used (Total process time): real time 0.68 seconds cpu time 0.68 seconds 34 35 data MyExcel2.table3; 36 set table3; 37 run; ERROR: Execute: Unable to insert row NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 65536 observations read from the data set WORK.TABLE3. WARNING: The data set MYEXCEL2.table3 may be incomplete. When this step was stopped there were 65535 observations and 5 variables. ERROR: ROLLBACK issued due to errors for data set MYEXCEL2.table3.DATA. NOTE: DATA statement used (Total process time): real time 0.68 seconds cpu time 0.68 seconds WARNING: Apparent invocation of macro EXPORTXL not resolved. WARNING: Apparent invocation of macro EXPORTXL not resolved. WARNING: Apparent invocation of macro EXPORTXL not resolved. 38 39 %exportxl(data=table1,outfile=c:\temp\MyWorkBook3.xls,type=N) - 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 40 %exportxl(data=table2,outfile=c:\temp\MyWorkBook3.xls,type=A) 41 %exportxl(data=table3,outfile=c:\temp\MyWorkBook3.xls,type=A)
Xia Keshan
Arthur,
I also get errors , That is the reason why I change it into XLS .
380 options stimer; 381 data table1 (drop=i); 382 set sashelp.class; 383 do i=1 to 3440; 384 output; 385 end; 386 run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.TABLE1 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.18 seconds cpu time 0.04 seconds 387 388 data table2; 389 set table1; 390 run; NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE2 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.01 seconds 391 392 data table3; 393 set table1; 394 run; NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE3 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.01 seconds 395 396 libname MyExcel1 excel "d:\MyWorkBook1.xlsx"; ERROR: Connect: ??????????????? ERROR: Error in the LIBNAME statement. 397 398 proc datasets library = MyExcel1 nolist; ERROR: Libname MYEXCEL1 is not assigned. 399 copy in = work out = MyExcel1; NOTE: Statements not processed because of errors noted above. 400 select table1 table2 table3; 401 run; ERROR: Libname MYEXCEL1 is not assigned. NOTE: Statements not processed because of errors noted above. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.16 seconds cpu time 0.06 seconds NOTE: The SAS System stopped processing this step because of errors. 402 403 libname MyExcel2 excel "d:\MyWorkBook2.xlsx"; ERROR: Connect: ??????????????? ERROR: Error in the LIBNAME statement. 404 405 data MyExcel2.table1; 406 set table1; 407 run; ERROR: Libname MYEXCEL2 is not assigned. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 408 409 data MyExcel2.table2; 410 set table2; 411 run; ERROR: Libname MYEXCEL2 is not assigned. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 412 413 data MyExcel2.table3; 414 set table3; 415 run; ERROR: Libname MYEXCEL2 is not assigned. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 416 417 %exportxl(data=table1,outfile=d:\MyWorkBook3.xlsx,type=N) NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.12 seconds cpu time 0.01 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00008, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:31:14, Create Time=02Oct2014:19:31:14 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: DATA statement used (Total process time): real time 1.96 seconds cpu time 1.87 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:31:16, Create Time=02Oct2014:19:31:16 NOTE: 23 records were written to the file DUMMY1. The minimum record length was 5. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.07 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 2.65 seconds cpu time 0.07 seconds 428 %exportxl(data=table2,outfile=d:\MyWorkBook3.xlsx,type=A) NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE2. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00010, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:31:18, Create Time=02Oct2014:19:31:18 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE2. NOTE: DATA statement used (Total process time): real time 1.89 seconds cpu time 1.88 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:31:20, Create Time=02Oct2014:19:31:16 NOTE: 20 records were written to the file DUMMY1. The minimum record length was 11. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 1.83 seconds cpu time 0.06 seconds 439 %exportxl(data=table3,outfile=d:\MyWorkBook3.xlsx,type=A) NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE3. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00012, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:31:22, Create Time=02Oct2014:19:31:22 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE3. NOTE: DATA statement used (Total process time): real time 1.90 seconds cpu time 1.93 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:31:24, Create Time=02Oct2014:19:31:16 NOTE: 20 records were written to the file DUMMY1. The minimum record length was 11. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.17 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.93 seconds cpu time 0.06 seconds
Arthur, I understand the problem with UE now.
Reverse engineering what is done in the code. It copies all data to the clipboard. Than it is pasted into the sheet. Very fast on a local system if you have a clipboard as it is in-memory. The UE does not support the clipboard not having the DMS. I was looking why it is done this way and could not found a VB object call to import data. Import a XML could be an option,. I am not having experience with that.
Hi Arthur,
Finally, I geti it running .
MyWorkBook1.xls 6KB
MyWorkBook2.xls 6KB
MyWorkBook3.xls can't find
450 options stimer; 451 data table1 (drop=i); 452 set sashelp.class; 453 do i=1 to 3440; 454 output; 455 end; 456 run; NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: The data set WORK.TABLE1 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 457 458 data table2; 459 set table1; 460 run; NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE2 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.11 seconds cpu time 0.04 seconds 461 462 data table3; 463 set table1; 464 run; NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.TABLE3 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 465 466 libname MyExcel1 excel "d:\MyWorkBook1.xls"; NOTE: Libref MYEXCEL1 was successfully assigned as follows: Engine: EXCEL Physical Name: d:\MyWorkBook1.xls 467 468 proc datasets library = MyExcel1 nolist; 469 copy in = work out = MyExcel1; 470 select table1 table2 table3; 471 run; NOTE: Copying WORK.TABLE1 to MYEXCEL1.TABLE1 (memtype=DATA). NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set MYEXCEL1.TABLE1 has 65360 observations and 5 variables. NOTE: Copying WORK.TABLE2 to MYEXCEL1.TABLE2 (memtype=DATA). NOTE: There were 65360 observations read from the data set WORK.TABLE2. NOTE: The data set MYEXCEL1.TABLE2 has 65360 observations and 5 variables. NOTE: Copying WORK.TABLE3 to MYEXCEL1.TABLE3 (memtype=DATA). NOTE: There were 65360 observations read from the data set WORK.TABLE3. NOTE: The data set MYEXCEL1.TABLE3 has 65360 observations and 5 variables. 472 473 libname MyExcel2 excel "d:\MyWorkBook2.xls"; NOTE: Libref MYEXCEL2 was successfully assigned as follows: Engine: EXCEL Physical Name: d:\MyWorkBook2.xls 474 NOTE: PROCEDURE DATASETS used (Total process time): real time 2.22 seconds cpu time 2.09 seconds 475 data MyExcel2.table1; 476 set table1; 477 run; NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: The data set MYEXCEL2.table1 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.71 seconds cpu time 0.67 seconds 478 479 data MyExcel2.table2; 480 set table2; 481 run; NOTE: There were 65360 observations read from the data set WORK.TABLE2. NOTE: The data set MYEXCEL2.table2 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.68 seconds cpu time 0.67 seconds 482 483 data MyExcel2.table3; 484 set table3; 485 run; NOTE: There were 65360 observations read from the data set WORK.TABLE3. NOTE: The data set MYEXCEL2.table3 has 65360 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.71 seconds cpu time 0.71 seconds 486 487 %exportxl(data=table1,outfile=d:\MyWorkBook3.xls,type=N) NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE1. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00014, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:33:27, Create Time=02Oct2014:19:33:27 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE1. NOTE: DATA statement used (Total process time): real time 1.93 seconds cpu time 1.88 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:33:29, Create Time=02Oct2014:19:31:16 NOTE: 23 records were written to the file DUMMY1. The minimum record length was 5. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 2.57 seconds cpu time 0.09 seconds 498 %exportxl(data=table2,outfile=d:\MyWorkBook3.xls,type=A) NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE2. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00016, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:33:31, Create Time=02Oct2014:19:33:31 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE2. NOTE: DATA statement used (Total process time): real time 1.90 seconds cpu time 1.90 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:33:33, Create Time=02Oct2014:19:31:16 NOTE: 20 records were written to the file DUMMY1. The minimum record length was 11. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 1.02 seconds cpu time 0.12 seconds 509 %exportxl(data=table3,outfile=d:\MyWorkBook3.xls,type=A) NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 1 observations read from the data set WORK.TABLE3. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: There were 1 observations read from the data set WORK.T_E_M_P. NOTE: The data set WORK.T_E_M_P has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file CODE2INC is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\#LN00018, RECFM=V,LRECL=256,File Size (bytes)=0, Last Modified=02Oct2014:19:33:34, Create Time=02Oct2014:19:33:34 NOTE: 10 records were written to the file CODE2INC. The minimum record length was 15. The maximum record length was 50. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: There were 65360 observations read from the data set WORK.TABLE3. NOTE: DATA statement used (Total process time): real time 1.97 seconds cpu time 1.95 seconds NOTE: The file DUMMY1 is: Filename=C:\Users\asus\AppData\Local\Temp\SAS Temporary Files\_TD6884\PasteIt.vbs, RECFM=V,LRECL=512,File Size (bytes)=0, Last Modified=02Oct2014:19:33:36, Create Time=02Oct2014:19:31:16 NOTE: 20 records were written to the file DUMMY1. The minimum record length was 11. The maximum record length was 70. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: DATA statement used (Total process time): real time 1.09 seconds cpu time 0.01 seconds
I also tried your program, but ran it on a SAS server (I don't have local SAS). I had to change the calling of your exportxl macro to type=P to avoid the clipboard method.
Both the PROC DATASETS and DATA step methods ran in about 4:45 (for all three workbooks) - workbook size was 57MB each and was being written to a network share so I'm not surprised it is slower than KSharp's test.
The exportxl was a little slower 7:20. The funny thing was MS Office appeared to be caching the workbooks during PROC DATASETS and DATA step methods as they weren't fully populated when SAS finished processing.
So for this particular test in our environment PROC EXPORT appears to be faster because workbooks are available as soon as SAS finishes because it doesn't use MS Office caching.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.