Date:         Fri, 14 Jan 2011 12:15:25 -0800
Reply-To:     mlhoward@avalon.net
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Excel tagsets - dropped zeros and numbers as text.
Comments: To: Sterling Paramore <gnilrets@GMAIL.COM>
Content-Type: text/plain; charset="UTF-8"

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;