ods tagsets.excelxp - one excel with few worksheets

[miri]

In the past when I submitted the kod:

ods results off;

  ods listing close;

ods tagsets.excelxp file="&outfiles.\&file_prefix._r_summed.xls"
style=sasweb ;

ods tagsets.excelxp options(sheet_name='parameters');

proc print data=opal_pen.&file_prefix._parameters noobs;run;quit;

ods tagsets.excelxp options(sheet_name='pen_r');

proc print data=opal_pen.&file_prefix._pen_r noobs;  run;quit;

ods tagsets.excelxp close;

I got one excel file with the sheets:  parameters and pen_r

Now when I submit it I get 2 excel file:

   
\outfiles\had_mirib_04DEC121104_r_summed.xls

   
\outfiles\had_mirib_04DEC121104_r_summed1.xls

What do I have to do to get only one file with 2 sheets ?

[Andre]

quick hypothesis : are you sure you were submitting the first quit; ?

[Andre]

With this test, i have only ONE file  (xml opened under excel2010)

ods results off;

* ods listing close;

ods tagsets.excelxp file="d:\temp\tworeally.xls" style=sasweb ;

ods tagsets.excelxp options(sheet_name='parameters');

proc print data=sashelp.class noobs;run;quit;

ods tagsets.excelxp options(sheet_name='pen_r');

proc print data=sashelp.air noobs;  run;quit;

ods tagsets.excelxp close;

So     scratch the two files and try again

[miri]

yes.

see the log of a similar program:

  ods results off;

10388              ods listing close;

10389

10390              ods tagsets.excelxp file="&outfiles.\a.xls" style=sasweb;

NOTE: Writing TAGSETS.EXCELXP Body file: C:\Amitim_Act_System\Insurants\outfiles\a.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.116, 08/25/10). Add options(doc='help')

to the ods statement for more information.

10391                  proc print data=opal_am.&file_prefix._parameters noobs;run;

NOTE: Writing HTML Body file: sashtml22.htm

NOTE: There were 38 observations read from the data set OPAL_AM.MAK_PL_M_05DEC121251_PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.13 seconds

      cpu time            0.09 seconds

　

10391!                                                                            quit;

10392

10393                  ods tagsets.excelxp options(sheet_name='פרמטרים');

10394                  proc print data=opal_am.&file_prefix._parameters noobs;run;

NOTE: Writing HTML Body file: sashtml23.htm

NOTE: Writing TAGSETS.EXCELXP Body file: C:\Amitim_Act_System\Insurants\outfiles\a1.xls

NOTE: There were 38 observations read from the data set OPAL_AM.MAK_PL_M_05DEC121251_PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.15 seconds

      cpu time            0.15 seconds

　

10394!                                                                            quit;

10395                      *format &final_vars comma20.2;

10396              *ods tagsets.excelxp file="&outfiles.\a.xls" style=sasweb;

10397

10398                  ods tagsets.excelxp options(sheet_name='התחייבויות למבוטח');

10399

10400              *   run;

10401

10402              ods tagsets.excelxp close;

[Andre]

I was submitting under sas 9.3.2  with tagsets excelxp

v1.130, 05/01/2012"

and after reading the comment of the maintainer inside the code, i did not see any comment about

a correction of sheetname.

So i suggest that you change your locale hebrew?  code in the sheetname into english one to see if there is

some change in the behavior

otherwise refer to the sas support help

Andre

[miri]

when I submited your test:

results off;

ods listing close;

tagsets.excelxp file="c:\temp\tworeally.xls" style=sasweb ;

tagsets.excelxp options(sheet_name='parameters');

print data=/*sashelp.class*/parameters noobs;run;quit;

tagsets.excelxp options(sheet_name='pen_r');

print data=/*sashelp.air*/ parameters noobs;  run;quit;

tagsets.excelxp close;

I still got 2 files. the log:

10448  ods results off;

10449

10450   ods listing close;

10451

10452  ods tagsets.excelxp file="c:\temp\tworeally.xls" style=sasweb ;

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\tworeally.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.116, 08/25/10). Add options(doc='help')

to the ods statement for more information.

10453

10454  ods tagsets.excelxp options(sheet_name='parameters');

10455

10456  proc print data=/*sashelp.class*/parameters noobs;run;

NOTE: Writing HTML Body file: sashtml29.htm

NOTE: There were 34 observations read from the data set WORK.PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.07 seconds

      cpu time            0.04 seconds

10456!                                                       quit;

10457

10458  ods tagsets.excelxp options(sheet_name='pen_r');

10459

10460  proc print data=/*sashelp.air*/ parameters noobs;  run;

NOTE: Writing HTML Body file: sashtml30.htm

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\tworeally1.xls

NOTE: There were 34 observations read from the data set WORK.PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.14 seconds

      cpu time            0.10 seconds

10460!                                                        quit;

10461

10462  ods tagsets.excelxp close;

[Robert_Bardos]

It's been a while since I used tagsets.excelxp so I may be totally off track.

Studying your log I see that both a HTML and an XLS file are being created for both your PROC PRINTs.

If that is how it should be and has always been then disregard my question, which is:

Have you always had an open HTML destination when creating your excel files?

(This is in the spirit of the eternal question "what changed?" when someones post a "last time it worked" type of question.)

Robert

[miri]

thank you !

when I canceled the option of creating html for result I finly got only one file:

10463  ods results off;

10464

10465   ods listing close;

10466

10467  ods tagsets.excelxp file="c:\temp\tworeally.xls" style=sasweb ;

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\tworeally.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.116, 08/25/10). Add options(doc='help')

to the ods statement for more information.

10468

10469  ods tagsets.excelxp options(sheet_name='parameters');

10470

10471  proc print data=/*sashelp.class*/parameters noobs;run;

NOTE: There were 34 observations read from the data set WORK.PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.09 seconds

      cpu time            0.03 seconds

10471!                                                       quit;

10472

10473  ods tagsets.excelxp options(sheet_name='pen_r');

10474

10475  proc print data=/*sashelp.air*/ parameters noobs;  run;

NOTE: There were 34 observations read from the data set WORK.PARAMETERS.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.03 seconds

      cpu time            0.03 seconds

10475!                                                        quit;

10476

10477  ods tagsets.excelxp close;

[Andre]

(I am back and saw Robert suggest.)

I conclude that in 9.3.2 this problem does not occur more as

ods results=on;

ods html ; * in case of doubt;

proc sql;select * from dictionary.destinations; quit;

ods results off;

* ods listing close;

ods tagsets.excelxp file="d:\temp\tworeally.xls" style=sasweb ;

ods tagsets.excelxp options(sheet_name='parameters');

proc print data=sashelp.class noobs;run;quit;

ods tagsets.excelxp options(sheet_name='pen_r');

proc print data=sashelp.air noobs;  run;quit;

ods tagsets.excelxp close;

i obtain only one file

and the sql is returning

the open destination in 9.3... by default