Date:         Wed, 29 Dec 2010 11:52:17 -0500
Reply-To:     Paul St Louis <PSTLOUI@DOT.STATE.TX.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul St Louis <PSTLOUI@DOT.STATE.TX.US>
Subject:      Re: Fun With SAS - My Vote for Oddest SAS Programming Language
              "Feature"
Content-Type: text/plain; charset=ISO-8859-1

At SCSUG I was excited to find out about how to use ODS to generate Excel
Pivot tables. You can read more about it with
http://support.sas.com/resources/papers/proceedings10/003-2010.pdf

I'm still learning, but I did find two oddities of sorts. One is that I
prefer showing certain variables in the pivot table with an embedded space,
such as 'Calendar Year' instead of 'Calendar_Year'. Use of the standard
Proc Print label and Alt-255 inserts the 'hidden dragon', a blank space, so
that the variable is indeed shown as 'Calendar Year'.

Second oddity, the options for creating the pivot table now require using
the variable label, i.e. such as ="Calendar Year" (without label, variable
is 'year') Using 'year' no longer works. Example,

ods tagsets.tableeditor file="TEMP_BY_GRP.html"
options(
sheet_name="Item Stats by Group"
button_text="Create Pivot Table"
auto_excel="yes"
excel_open="yes"
pivotrow="Item Group,Item"
pivotcol="Calendar Year"
pivotdata="Estimated Quantity,Extended Amount"
pivotdata_stats="Sum"
pivotpage="State or Local,Contract Type");
proc print data=pivot.fin NOOBS LABEL;
  FORMAT BTEXTAMT DOLLAR18.2;
  VAR ITEM_DSH IT_GRP_UOM TYPE YEAR KIND IDESCR IUNITS BTUPRICE BTEXTAMT
BTOQTY IAVG;
LABEL
  ITEM_DSH  = 'Item'
  IT_GRP_UOM= 'Item Group'
  TYPE      = 'Contract Type'
  YEAR      = 'Calendar Year'
  KIND      = 'State or Local'
  IDESCR    = 'Item Description'
  IUNITS    = 'UOM'
  BTUPRICE  = 'Item Price'
  BTEXTAMT  = 'Extended Amount'
  BTOQTY    = 'Estimated Quantity'
  IAVG      = 'Item Average'
;
TITLE 'Test SAS Pivot Table Build Capabilities';
RUN;
ods tagsets.tableeditor close;