|
Excel 2007 may be the problem. I am using Excel(r) 2003 and Excel(r)
tagset version 1.116. I do not know what, if anything SAS will do with
the Excel(r) tagsets for Excel(r) 2007. I think that there are other
SAS-L threads dealing with problems that people are having with SAS and
Excel(r) 2007.
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sterling Paramore
Sent: Friday, January 14, 2011 6:02 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Excel tagsets - dropped zeros and numbers as text.
It doesn't seem to be working for me (btw, I'm using Excel 2007). Using
this code, I'm still getting the negative numbers show up as text:
ods tagsets.excelxp file="/sas/DataStore/shared/secure/test.xls"
style=statistical
;
proc report data = SASUSER.example;
columns Claim_Class_Id Claim_Membs_Age Claim_Adjustment_Flag
ClaimLine_Allow_Amt;
define Claim_Class_Id / group style(column)={tagattr='format:text'};
define Claim_Membs_Age / group;
define Claim_Adjustment_Flag / group;
define ClaimLine_Allow_Amt / analysis sum
style(column)={tagattr='format:$#,##0.00' just=R};
run;
ods tagsets.excelxp close;
On Fri, Jan 14, 2011 at 2:29 PM, Kirby, Ted <ted.kirby@lewin.com> wrote:
> Sterling,
>
>
>
> The key is to get the correct format in the tagattr part of the code.
> This code will correct the problems you are having:
>
>
>
> proc report data = example;
>
> columns Claim_Class_Id Claim_Membs_Age Claim_Adjustment_Flag
> ClaimLine_Allow_Amt;
>
>
>
> define Claim_Class_Id / group style(column)=[tagattr='format:0000'
> just=l];
>
> define Claim_Membs_Age / group;
>
> define Claim_Adjustment_Flag / group;
>
> define ClaimLine_Allow_Amt / analysis sum
> style(column)=[tagattr='format:$#,##0.00' just=R];
>
>
>
> run;
>
>
>
> You may still have some "prettying up" to do to make the Excel file
look
> good. Using the default Excel tagsets, when I run this code, the
> headings are much larger than the rest of the table, but this can be
> fixed with the appropriate style changes or template definitions.
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Sterling Paramore
> Sent: Friday, January 14, 2011 3:19 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Excel tagsets - dropped zeros and numbers as text.
>
>
>
> That seems to work for the leading zeros, but it's still importing the
>
> negative dollar amounts as text and applying the selected format to
the
>
> positive values.
>
>
>
> -Sterling
>
>
>
> On Fri, Jan 14, 2011 at 12:15 PM, Mary <mlhoward@avalon.net> wrote:
>
>
>
> > Tagattr are the way to get these- these formats are found in Excel
> under
>
> > Formats and you can use them in your SAS program to get the format
> that you
>
> > want in Excel, such as:
>
> >
>
> > define subset/display "Subset" style(column)=[tagattr='format:text'
> just=l]
>
> > style(header)=[background=lightgrey Just=l];
>
> > define n/display "n" style(column)=[tagattr='format:#,##0' just=R]
>
> > style(header)=[background=lightgrey Just=r];
>
> >
>
> > -Mary
>
> >
>
> >
>
> >
>
> > --- gnilrets@GMAIL.COM wrote:
>
> >
>
> > From: Sterling Paramore <gnilrets@GMAIL.COM>
>
> > To: SAS-L@LISTSERV.UGA.EDU
>
> > Subject: Excel tagsets - dropped zeros and numbers as text.
>
> > Date: Fri, 14 Jan 2011 11:51:36 -0800
>
> >
>
> > Dear SAS-L,
>
> >
>
> > I'm playing around with getting nice formatted reports into Excel
> documents
>
> > and automated to the extent that I don't have to do any further
>
> > manipulation
>
> > in Excel to get what I want (although the end user's may want to do
>
> > manipulation in Excel). One of the options I'm exploring now is
using
>
> > Excel
>
> > tagsets, but I'm running into problems. Take the example data and
> code
>
> > below. The *character *variable Claim_Class_Id is a zero-filled
> number.
>
> > However, the resulting Excel document treats the column as a number
> and
>
> > removes the leading zeros. Conversely, the negative dollar amounts
> are
>
> > treated as a text string and therefore don't sum properly (the
> positive
>
> > dollar amounts are, correctly, numbers formatted as dollars). Is
> there any
>
> > way to fix these kinds of issues with Excel tagsets? If not, does
> anyone
>
> > have a good suggestion for how to solve my problem of getting nice
>
> > formatted
>
> > reports into Excel (we have AMO too, but it suffers from basically
the
> same
>
> > kinds of problems)?
>
> >
>
> > Thanks,
>
> > Sterling
>
> >
>
> >
>
> >
>
> > Claim_ Claim_
>
> > Claim_ Membs_
> Adjustment_
>
> > Obs Class_Id Age Flag
>
> > ClaimLine_Allow_Amt
>
> >
>
> > 1 0001 00-04 A
>
> > $7,892.63
>
> > 2 0001 00-04 B
>
> > $-7,892.63
>
> > 3 0001 00-04 F
>
> > $43,503.42
>
> > 4 0001 05-18 A
>
> > $10,067.73
>
> > 5 0001 05-18 B
>
> > $-10,067.73
>
> > 6 0001 05-18 F
>
> > $177,332.47
>
> > 7 0001 19-23 A
>
> > $2,074.87
>
> > 8 0001 19-23 B
>
> > $-2,074.87
>
> > 9 0001 19-23 F
>
> > $125,069.37
>
> >
>
> >
>
> > ods tagsets.excelxp file="/sas/DataStore/shared/secure/test.xls"
>
> > style=statistical;
>
> >
>
> > proc report data = example;
>
> > columns Claim_Class_Id Claim_Membs_Age Claim_Adjustment_Flag
>
> > ClaimLine_Allow_Amt;
>
> >
>
> > define Claim_Class_Id / group;
>
> > define Claim_Membs_Age / group;
>
> > define Claim_Adjustment_Flag / group;
>
> > define ClaimLine_Allow_Amt / analysis sum;
>
> >
>
> > run;
>
> >
>
> > ods tagsets.excelxp close;
>
> >
>
> >
>
> >
>
>
> ************* IMPORTANT - PLEASE READ ********************
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information,
> and may be used only by the person or entity to which it is addressed.
If
> the reader of this
> e-mail is not the intended recipient or his or her authorized agent,
the
> reader is hereby
> notified that any dissemination, distribution or copying of this
e-mail is
> prohibited. If you
> have received this e-mail in error, please notify the sender by
replying to
> this message
> and delete this e-mail immediately.
>
>
************* IMPORTANT - PLEASE READ ********************
This e-mail, including attachments, may include confidential and/or proprietary information,
and may be used only by the person or entity to which it is addressed. If the reader of this
e-mail is not the intended recipient or his or her authorized agent, the reader is hereby
notified that any dissemination, distribution or copying of this e-mail is prohibited. If you
have received this e-mail in error, please notify the sender by replying to this message
and delete this e-mail immediately.
| 
