Date:         Wed, 16 Mar 2005 11:15:01 -0500
Reply-To:     "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Excel worksheet labels with excelxp tagset
Comments: To: Penn Whipple <pennwhip@YAHOO.COM>

On Wed, 16 Mar 2005 09:09:18 -0500, Penn Whipple <pennwhip@YAHOO.COM> wrote:

>Hi All,
>
>Is there a way to custom label seperate Excel worksheets when using the
>excelxp tagset? I'm using proc report within the excelxp tagset as well...
>
>ods tagsets.excelxp file="myfile.xml"
>path="mypath";
>
>proc report data=mydataset;
>.
>.
>.
>run;
>porc report data=mydataset2;
>.
>.
>.
>run;
>
>ods tagsets.excelxp close;

Hi, Penn,

Here is one way to do that by tweaking the excelXP tagset. There probably is
an easier way -- the way it goes with my sas-l post recently -- there *must*
be an easier way. ... :-)

Here I am inheriting everything from tagsets.excelXP and add one macro
variable, and use it in one event that is replaced.

If you don't want to mess with macro variables, then there may also be a way
to tweak the template using options() hash that tagsets.excelxp features --
but I have not tried it, yet. See the remplate source by doing:

proc template;
  source tagsets.excelXP;
run;

To see the options mechanism for this template working, submit the following
and see the log:

ods tagsets.excelXP file="quick.xml" options(myOption="option");
  /* anything that generates some output will do */
  proc print data=sashelp.class(obs=1);
  run;
ods tagsets.excelXP close;


Cheers,
Chang

<sasl:code ver="9.1" sysscp="WIN">
%let pwd=%sysfunc(pathname(WORK));
%put NOTE: pwd=&pwd.;
x cd "&pwd.";


ods path (prepend) work.myTmp(update);

  proc template;
    define tagset myTmp.myExcelXP;
      parent = tagsets.excelXP;

      /* add one macro var */
      mvar myLabel;

      /* and replace one event */
      define event worksheet_label;
        do /if label;
           set $label label;
        else;
           set $label proc_name;
        done;

        do /if cmp( $sheet_interval, "none");
           set $worksheetName "Job " total_page_count " - " $label;
        else /if cmp( $sheet_interval, "proc");
           set $worksheetName "Proc " total_proc_count " - " $label;
        else /if cmp( $sheet_interval, "page");
           set $worksheetName "Page " total_page_count " - " $label;
        else /if cmp( $sheet_interval, "bygroup");
           set $worksheetName "By " $numberOfWorksheets " " $byGroupLabel "
- " $label;
        else /if cmp( $sheet_interval, "table");
           /* set $worksheetName "Table " $numberOfWorksheets " - " $label; */
           set $worksheetName myLabel;
        done;

        do /if $byGroupLabel;
           set $worksheetName "By " $numberOfWorksheets " " $byGroupLabel "
- " $label;
        done;

        unset $byGroupLabel;
        unset $label;
      end;
    end;
  run;


  ods myTmp.myExcelXP file="test.xml";

    %let myLabel=names;
    proc report data=sashelp.class(obs=5) nowd missing;
      columns name;
      define name/display;
    run;

    %let myLabel=heights;
    proc report data=sashelp.class(obs=5) nowd missing;
      columns height;
      define  height/display;
    run;

  ods myTmp.myExcelXP close;

ods path reset;
</sasl:code>