Solved: Re: Anyone have time to run a test?

art297 · Posted 09-30-2014 09:01 AM

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

jakarman · Posted 10-01-2014 11:26 AM

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.

---->-- ja karman --<-----

View solution in original post

Ksharp · Posted 09-30-2014 09:34 AM

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

art297 · Posted 09-30-2014 09:44 AM

: .9.2 is sufficiently recent for this test. Sleep? Isn't it only 9:45pm there now?

Ksharp · Posted 10-01-2014 09:45 AM

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

art297 · Posted 10-01-2014 10:17 AM

: It's not a 9.2 problem but, rather, the fact that you changed the extension from xlsx to xls. Xls files can only handle 65536 rows. In creating the test files, just use 1 to 3440, rather than 1 to 50000.

Ksharp · Posted 10-02-2014 07:27 AM

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

jakarman · Posted 10-01-2014 11:26 AM

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.

---->-- ja karman --<-----

Ksharp · Posted 10-02-2014 07:38 AM

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

art297 · Posted 10-02-2014 10:02 AM

: You are absolutely correct .. our suggested macro approach is only viable for stand alone windows machines.

SASKiwi · Posted 10-02-2014 09:49 PM

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.

Classroom Training Available!