Date: Fri, 14 Jan 2011 17:29:43 -0500
Reply-To: "Kirby, Ted" <ted.kirby@LEWIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Kirby, Ted" <ted.kirby@LEWIN.COM>
Subject: Re: Excel tagsets - dropped zeros and numbers as text.
In-Reply-To: A<AANLkTi=6vCP_KD_5vsV_JkKiVENZ0MgP-DrWBw7SEBFP@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"
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.
