topic Re: ods tagsets.excelxp - one excel with few worksheets in SAS Programming

ods tagsets.excelxp - one excel with few worksheets

miri — Thu, 06 Dec 2012 09:17:27 GMT

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 ?

Re: ods tagsets.excelxp - one excel with few worksheets

Andre — Thu, 06 Dec 2012 11:37:24 GMT

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

Re: ods tagsets.excelxp - one excel with few worksheets

Andre — Thu, 06 Dec 2012 11:46:37 GMT

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

Re: ods tagsets.excelxp - one excel with few worksheets

miri — Thu, 06 Dec 2012 11:47:59 GMT

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;

Re: ods tagsets.excelxp - one excel with few worksheets

Andre — Thu, 06 Dec 2012 11:56:07 GMT

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

Re: ods tagsets.excelxp - one excel with few worksheets

miri — Thu, 06 Dec 2012 11:57:45 GMT

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;

Re: ods tagsets.excelxp - one excel with few worksheets

Robert_Bardos — Thu, 06 Dec 2012 11:59:46 GMT

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

Re: ods tagsets.excelxp - one excel with few worksheets

miri — Thu, 06 Dec 2012 12:10:32 GMT

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;

Re: ods tagsets.excelxp - one excel with few worksheets

Andre — Thu, 06 Dec 2012 13:26:46 GMT

(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