Look at the 'TAGATTR' option, which you can use to tell Excel how to format a numeric value.

-Joe

On Fri, Jan 14, 2011 at 1:51 PM, Sterling Paramore <gnilrets@gmail.com>wrote:

> 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; >