BookmarkSubscribeRSS Feed
miri
Calcite | Level 5

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 sheetsparameters 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 ?

8 REPLIES 8
Andre
Obsidian | Level 7

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

Andre
Obsidian | Level 7

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
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

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 noobsrun;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
Fluorite | Level 6

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
Calcite | Level 5

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
Obsidian | Level 7

(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

temp.png

the open destination in 9.3... by default

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1324 views
  • 3 likes
  • 3 in conversation