topic Re: Take time to open XLS spread sheet. in Microsoft Integration with SAS

Take time to open XLS spread sheet.

Inp — Wed, 29 Aug 2007 21:49:17 GMT

Hi I have ran with the following code with 30,000 records and it took only 12 sec , but I am not able to open the xls file and take so long to open a file with 5000 records. When I changed ods msoffice2k to ExcelXP and removed all fomatting in the proc report and ran with 30,000 records, it took 4 hours to run the program. Can any one give advice, how can I optimize this time issue or any thing wrong with my coding?

LIBNAME mylib 'C:\TEMP';
ods tagsets.msoffice2K path="C\TEMP" file=FILENAME.XLS rs=none
style=minimal options(doc='QUICK'
Sheet_Name='Report'
Orientation='LANDSCAPE'
Embedded_Titles='YES'
Embed_Titles_Once='YES'
Suppress_Bylines='YES'
Sheet_Interval='NONE'
Convert_Percentages='YES'
Skip_Space='3,0,1,1,1'
Absolute_Column_Width='40,7,12,9,9,9,10,7,10,9,9,10,9,9,9,9,9,9,9'
);
run;
TITLE;
OPTIONS NOBYLINE;
PROC REPORT DATA=mylib.INDATA NOWINDOWS;
COLUMN VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8
VAR9 VAR10 VAR11 VAR12 VAR13 VAR14 VAR15 VAR16
VAR17 VAR18 VAR19;
DEFINE VAR1 / DISPLAY 'VAR1'
STYLE(COLUMN)={HTMLSTYLE='mso-number-format:00000'};
DEFINE VAR2 / DISPLAY 'VAR2';
DEFINE VAR3 / DISPLAY 'VAR3'
STYLE(COLUMN)={HTMLSTYLE='mso-number-format:000000000000'};
DEFINE VAR4 / DISPLAY 'VAR4';
DEFINE VAR5 / DISPLAY 'VAR5';
DEFINE VAR6 / DISPLAY 'VAR6' FORMAT=COMMA13.;
DEFINE VAR7 / DISPLAY 'VAR7';
DEFINE VAR8 / DISPLAY 'VAR8'
STYLE(COLUMN)={HTMLSTYLE='mso-number-format:00000'};
DEFINE VAR9 / DISPLAY 'VAR9';
DEFINE VAR10 / DISPLAY 'VAR10';
DEFINE VAR11 / DISPLAY 'VAR11';
DEFINE VAR12 / DISPLAY 'VAR12'
STYLE(COLUMN)={HTMLSTYLE='mso-number-format:000000000000'};
DEFINE VAR13 / DISPLAY 'VAR13';
DEFINE VAR14 / DISPLAY 'VAR14';
DEFINE VAR15 / DISPLAY 'VAR15';
DEFINE VAR16 / DISPLAY 'VAR16';
DEFINE VAR17 / DISPLAY 'VAR17';
DEFINE VAR18 / DISPLAY 'VAR18';
DEFINE VAR19 / DISPLAY 'VAR19';
RUN;
ods _all_ close;
ods listing;
ods results;
ods trace off;

Thanks

Inp

Re: Take time to open XLS spread sheet.

Cynthia_sas — Wed, 29 Aug 2007 22:30:14 GMT

Hi:
You might consider contacting Tech Support for more help. At a quick glance, I note the following:
1) TAGSETS.MSOFFICE2K will NOT give you any error message on the use of ExcelXP options, but it will not use any of the ExcelXP options.

2) This is incorrect: path="C\TEMP" or else it's a typo...you need it to be "C:\Temp" (with a colon after the drive letter).

3) this is also incorrect: file=FILENAME.XLS
If you are specifying a file name, then you need to have the name in quotes: file="FILENAME.XLS" OR you need to have this:

4) HTMLSTYLE= override is the correct override for TAGSETS.MSOFFICE2K to send Microsoft formats to Excel. However, when you use TAGSETS.EXCELXP, you have to use the TAGATTR= style override to pass a Microsoft format from ODS to Excel.
[pre]
filename mywb 'c:\temp\mywb.xls';
ods tagsets.msoffice2k path='c:\temp' file=mywb;
[/pre]

When I ran a quick test using both tagsets on a 30000 obs, 19 var file, I did not see outrageous response times (see attached log)
[pre]
44176 data bigfile(keep=name age height weight var1-var15);
44177 set sashelp.class;
44178 var1 = age;
44179 var2 = age;
44180 var3 = age;
44181 var4 = age;
44182 var5 = age;
44183 var6 = height;
44184 var7 = height;
44185 var8 = height;
44186 var9 = height;
44187 var10 = height;
44188 var11 = weight;
44189 var12 = weight;
44190 var13 = weight;
44191 var14 = weight;
44192 var15 = weight;
44193 if _n_ le 3;
44194 do i = 1 to 10000 by 1;
44195 output;
44196 end;
44197 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.BIGFILE has 30000 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

44198
44199 ods msoffice2k path='c:\temp' (url=none)
44200 file='bigfile1.xls';
NOTE: Writing MSOFFICE2K Body file: bigfile1.xls
44201 proc report data=bigfile nowd;
44202 run;

NOTE: There were 30000 observations read from the data set WORK.BIGFILE.
NOTE: PROCEDURE REPORT used (Total process time):
real time 25.61 seconds
cpu time 24.29 seconds

44203 ods _all_ close;
44204
44205 ods tagsets.excelxp path='c:\temp' (url=none)
44206 file='bigfile2.xls';
NOTE: Writing TAGSETS.EXCELXP Body file: bigfile2.xls
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05). Add options(doc='help') to the
ods statement for more information.
44207
44208 proc report data=bigfile nowd;
44209 run;

NOTE: There were 30000 observations read from the data set WORK.BIGFILE.
NOTE: PROCEDURE REPORT used (Total process time):
real time 1:14.29
cpu time 1:14.10

44210 ods _all_ close;
[/pre]

I can't see that adding a bit more PROC REPORT or ExcelXP options -- even with style overrides -- would add that much more time. This problem is really a good one for Tech Support. If there is a huge performance difference using your data and your code (once it's corrected), then that is something that Tech Support can convey to the developers.

cynthia

Re: Take time to open XLS spread sheet.

Inp — Thu, 30 Aug 2007 14:11:44 GMT

Thanks Cynthia,
I want to make sure that I am using right SAS version.

My SAS version is : SAS (r) 9.1 (TS1M3)
OS : Windows 2000.
HardWare Pentium : Intel(R) Pentium(R)M Processor 1.60Hz.
Ram: 1 Gb.

Re: Take time to open XLS spread sheet.

Cynthia_sas — Thu, 30 Aug 2007 15:30:26 GMT

Hi:
MSOFFICE2K and ExcelXP tagsets are both available starting in SAS 9. Tech Support is really your best bet for help with this problem.

cynthia