Date:         Fri, 19 Sep 2008 10:19:37 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: follow up on excel/ ods q
Comments: To: Melissa <alfano.melissa@gmail.com>
Content-Type: text/plain; charset="iso-8859-1"

Melissa,

It is not the sheet label that matters; it is the sheet name.

Please always respond to SAS-L rather than a person individually; I'll put this back to SAS-L in case someone wants to comment further.

-Mary
  ----- Original Message ----- 
  From: Melissa 
  To: Mary 
  Sent: Thursday, September 18, 2008 7:56 PM
  Subject: Re: follow up on excel/ ods q


  On Sep 18, 3:43 pm, mlhow...@avalon.net (Mary) wrote:
  > Melissa,
  >
  > It is the statement: ods tagsets.excelxp options(sheet_name="Control Raw
  > Data") that changes the sheet name.
  >
  > If you are running two different macros and want them in the same workbook,
  > then take the declare to the tagset out of the macro where you are changing
  > sheets, and put it before the call to the two macros, and then close the
  > workbook after the two macros:
  >
  > %macro exporttab2 (var= );
  >
  > ods tagsets.excelxp options(sheet_name="&month. Adjusted");
  > title '&month. Adjusted';
  > proc print label data=&month..tab2_carrier&var. noobs;
  > var rte_id cnsgnmt_dt cnsgnmt_tm CNSGNMT_NBR ;
  > label
  > rte_id='Carrier ID'
  > cnsgnmt_dt='Consignment Date'
  > cnsgnmt_tm='Consignment Time'
  > CNSGNMT_NBR='Consignment number'
  >
  > ;
  > run;
  >
  > ods tagsets.excelxp close;
  > ods listing;
  >
  > %mend;
  >
  > filename excel "E:\Engine\&month.\Carrier&var..xls";
  >
  > ods listing close;
  > ods tagsets.excelxp file=excel
  > style=analysis
  > options(absolute_column_width='20,10,8,10,10,8,15,8,15,8,15,15,8,15,8,15,10­,10,20'
  > sheet_label=' &month. Adjusted');
  > %macro exporttab1;
  > %macro exporttab2;
  > ods tagsets.excelxp close;
  > ods listing;
  >
  > -Mary
  >
  >
  >
  > ----- Original Message -----
  > From: Melissa
  > To: SA...@LISTSERV.UGA.EDU
  > Sent: Thursday, September 18, 2008 2:21 PM
  > Subject: follow up on excel/ ods q
  >
  > I am using ODS to output to excel. I need to set it up so I can
  > output to two different worksheets within the spreadsheet (I am using
  > two ODS macros).
  >
  > Is there anything I can put in my code to fix it?
  >
  > Here is my code:
  > %macro exporttab2 (var= );
  >
  > filename excel "E:\Engine\&month.\Carrier&var..xls";
  >
  > ods listing close;
  > ods tagsets.excelxp file=excel
  > style=analysis
  > options(absolute_column_width='20,10,8,10,10,8,15,8,15,8,15,15,8,15,8,15,10­,10,20'
  > sheet_label=' &month. Adjusted');
  >
  > ods tagsets.excelxp options(sheet_name="&month. Adjusted");
  > title '&month. Adjusted';
  > proc print label data=&month..tab2_carrier&var. noobs;
  > var rte_id cnsgnmt_dt cnsgnmt_tm CNSGNMT_NBR ;
  > label
  > rte_id='Carrier ID'
  > cnsgnmt_dt='Consignment Date'
  > cnsgnmt_tm='Consignment Time'
  > CNSGNMT_NBR='Consignment number'
  >
  > ;
  > run;
  >
  > ods tagsets.excelxp close;
  > ods listing;
  >
  > %mend;- Hide quoted text -
  >
  > - Show quoted text -

  I am trying to figure out where to put the sheet_label name for my
  first macro.  Here is my general idea:

  ods listing close;
  ods tagsets.excelxp file=excel
  style=analysis
  options(absolute_column_width='20,10,8,10,10,8,15,8,15,8,15,15,8,15,8,15,10­,
  10,20'
  sheet_label=' Sheet 1');
  %macro exporttab1;
  options(absolute_column_width='20,10,8,10,10,8,15,8,15,8,15,15,8,15,8,15,10­,
  10,20'
  sheet_label=' &month. Adjusted');
  %macro exporttab2;
  ods tagsets.excelxp close;
  ods listing;


  Also, Mary you've been such a help to me with this ODS stuff already.
  Thank you so much.