Date:         Thu, 24 Jan 2008 08:48:38 -0600
Reply-To:     bruce johnson <chimanbj@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         bruce johnson <chimanbj@GMAIL.COM>
Subject:      Re: TAGSET.EXCELXP - not formatting my numeric variables
Comments: To: Yu Zhang <zhangyu05@gmail.com>
In-Reply-To:  <445d9dbe0801231339k5ba6a5a4waee32121635e8650@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

A typo!  :-)  I'll fix it...

the formats are in Excel, under format->cells->number->Custom.  The
easiest thing to do is to create a new workbook, enter the number or
text in a cell, and then save the workbook as an XML file.  Open that
file in a text editor, and you'll find the formula.

On Jan 23, 2008 3:39 PM, Yu Zhang <zhangyu05@gmail.com> wrote:
> hi, Bruce,
>
> Is it a typo? you defined a template and didn't use it with the Tagset
> statement. Also where we can find the Docs for those wierd looking format in
> EXCEL?
>
> Thanks!
>
> Yu
>
>
>
>
> On Jan 23, 2008 2:34 PM, bruce johnson <chimanbj@gmail.com> wrote:
>
> > You have to modify your template to include the format from Microsoft
> > Excel that will present your data properly.  Consider the following
> > code.  It's fully tested, and you can run it at your site to see the
> > resulting Excel file.
> >
> > filename output "c:\";
> > %include 'c:\sasdata\excelxp.sas';
> >
> > proc template;
> >  define style styles.denise;
> >  parent = styles.sansprinter;
> > style numeric_data from data /
> >  tagattr='format:#,##0;;'
> >  just = right;
> > style dollar_data from data /
> >  tagattr='format:_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;*
> > \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)'
> >  just = right;
> > end;
> > run;
> >
> > ods tagsets.excelxp path=output
> >                    file="shoe_report.xls"
> >                                        style=xlsansprinter
> >                                        ;
> > data shoes;
> >  set sashelp.shoes;
> >  stores=stores*100;
> > run;
> >
> > proc print data=shoes noobs label;
> >  var region product subsidiary;
> >  var sales inventory returns / style(data)=dollar_data;
> >  var stores / style(data)=numeric_data;
> > run;
> >
> > ods tagsets.excelxp close;
> >
> > Hope that helps!
> >
> >
> >
> >
> > On Jan 23, 2008 2:06 PM, Denise Kruse <krusesas@yahoo.com> wrote:
> > > In all of the papers that I have read, it indicates that this
> tagset.excelxp should automatically format with commas a numeric variable.
> However, this is not happening for me.  I even tried to insert the format
> for the column back into the proc report, but that did not help.
> > >
> > > I have verified that my x and tot_remain variables are NUM 8. by a
> simple proc contents.  I've been trying to find the answer for hours now.
> Has anyone had to troubleshoot this before?  Do you have any tips for me?
> > >
> > > Thanks,
> > > Denise Kruse
> > >
> > > Code:
> > > ods listing close;   run;
> > > ods tagsets.ExcelXP file='/mydir/ilovesas/waterfall_.xls'
> options(doc='help' );
> > >
> > > ods tagsets.ExcelXP options(sheet_name='Waterfall' embedded_titles='Yes'
> embedded_footnotes='Yes');
> > >
> > > proc report data=wrpt2 split='*' ls=256 headline nowd out=outprt;
> > > title1 "Waterfall Report";
> > > title2 "Total Accounts (Universe) " &title1_amt2.;
> > > footnote "Total Balance Leads (Eligible Accounts) " &eligible.;
> > > column y
> > >               x
> > >              TOT_REMAIN;
> > > define y / order=data 'Scrub';
> > > define x / order=data 'Amount';
> > > define TOT_REMAIN / order=data 'Remaining';
> > > run;
> > >
> > > ods tagsets.ExcelXP close;
> > > ods listing ;
> > > run;
> > >
> > >
> > >
> ____________________________________________________________________________________
> > > Never miss a thing.  Make Yahoo your home page.
> > > http://www.yahoo.com/r/hs
> > >
> >
>
>