Date:         Wed, 10 May 2006 07:58:58 -0400
Reply-To:     Scott Bass <sas_l_739@YAHOO.COM.AU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Scott Bass <sas_l_739@YAHOO.COM.AU>
Subject:      Re: PROC TABULATE vs. PROC REPORT

Thanks Jack (and Ya as well).

I'm going with the PROC REPORT approach, but am still having some problems.

Here is my current test code:

options formchar="|----|+|---+=|-/\<>*";

proc format;
   value widget
      1="WidgetA"
      2="WidgetB"
      3="WidgetC"
     .Z="Total:"
   ;
run;

data test;
   length _name 8 name $10 quantity price 8;
   format _name widget. price blanknum.;
   input _name name quantity price;
   cards;
1 WidgetA  1  10
1 WidgetA  2  10
1 WidgetA  3  20
2 WidgetB  4  20
2 WidgetB  5  20
3 WidgetC  6  30
;
run;

proc report data=test box nowd split="^";
   columns _name quantity price total;
   define  _name        / group    left  width=10 format=widget.;
   define  quantity     / sum      right width=10 format=10.;
   define  price        / group    right width=10 format=dollar10.;
   define  total        / computed left  width=10 format=dollar10.;

   rbreak after / summarize ol;

   compute _name;
      if _break_ = '_RBREAK_' then do;
         _name = .Z;
      end;
   endcomp;

   compute total;
      if _break_ = '_RBREAK_' then do;
         total = gtotal;
      end;
      else do;
         total = quantity.sum * price;
         gtotal + total;
      end;
   endcomp;
quit;

In my actual program, the first column is numeric.  So, my test now more
accurately represents my actual program.  The change to price in obs 3 is
intentional and not a typo; price hike :-).

1.  I can't ever get "Total:" to display.  Is my logic wrong in setting
_name = .Z in the compute block?

2.  Just an observation:  sometimes _name is a group variable, sometimes it
is an order variable (depending on the program).  I have to be careful and
be aware of the interaction between _name and price.

These work:

_name = group, price = group
_name = order, price = display

(It would be nice to order on name, but group on price.  I assume group
variables need to be to the left of order variables?  I could probably
accomplish this with reordering variables, noprinting some, and using
computed variables for display.  Not a big deal, just an observation.
Perhaps it's documented about the ordering of group vs. order variables)

These don't work:

_name = group, price = display (does not collapse on _name)
_name = order, price = group (wrong numbers, wrong bottom line)
_name = order, price = order (i think it's documented that all order
variables need to be on the left???)

Anyway, if someone could explain how to get #1 above to work, that would be
much appreciated.

Regards,
Scott

On Thu, 4 May 2006 20:45:43 -0700, Jack Hamilton <jfh@STANFORDALUMNI.ORG>
wrote:

>On 5/9/2006 7:26 PM Scott Bass said the following:
>> Hi,
>>
>> Sorry for what may be a really simple question...
>>
>> Test case (not my real code but illustrative of what I want to do):
>>
>> options formchar="|----|+|---+=|-/\<>*";
>>
>> data test;
>>    length name $10 quantity price 8;
>>    format price dollar.;
>>    input name quantity price;
>>    cards;
>> WidgetA  1  10
>> WidgetA  2  10
>> WidgetA  3  10
>> WidgetB  4  20
>> WidgetB  5  20
>> WidgetC  6  30
>> ;
>> run;
>>
>> proc tabulate;
>>    class name;
>>    var quantity price;
>>    table name all="Total Row", quantity price all="Total Col";
>> run;
>>
>> What I want is:
>>
>> * records collapsed on name
>> * no analysis on price (i.e. print 10 for A, 20 for B, 30 for C)
>
>There has to be some kind of analysis on price if it's not a class
>variable.  You could make it a class variable, but I don't think that
>would help.  Or you could use a non-totaling statistic like mean, min,
>or max (doesn't matter if you think they'll all me the same).
>
>proc tabulate;
>    class name;
>    var quantity price;
>    table name all="Total Row", quantity*sum='' price*mean='' all="Total
>Col"*n='';
>run;
>
>prints
>
>----------------------------------------------------------------
>|                       |  quantity  |   price    | Total Col  |
>|-----------------------+------------+------------+------------|
>|name                   |            |            |            |
>|-----------------------|            |            |            |
>|WidgetA                |        6.00|       10.00|        3.00|
>|-----------------------+------------+------------+------------|
>|WidgetB                |        9.00|       20.00|        2.00|
>|-----------------------+------------+------------+------------|
>|WidgetC                |        6.00|       30.00|        1.00|
>|-----------------------+------------+------------+------------|
>|Total Row              |       21.00|       16.67|        6.00|
>----------------------------------------------------------------
>
>
>> * the total column would be Quantity * Price
>
>I don't think you can do that in tabulate without preprocessing the data
>(which means you're not doing it in tabulate).  The only calculations
>you can do are of percentages.
>
>> * the total row would sum Quantity, not sum Price, and sum Total
>
>I don't think you can do that either.
>
>> The output would look something like (edited):
>>
>> --------------------------------------------------------
>> |              |  quantity  |   price    |   Total Col |
>> |--------------+------------+------------+--------------
>> |name          |            |            |             |
>> |--------------|            |            |             |
>> |WidgetA       |           6|       10.00|       60.00 |
>> |--------------+------------+------------+-------------|
>> |WidgetB       |           9|       20.00|      180.00 |
>> |--------------+------------+------------+-------------|
>> |WidgetC       |           6|       30.00|      180.00 |
>> |--------------+------------+------------+-------------|
>> |Total Row     |          21|            |      420.00 |
>> --------------------------------------------------------
>>
>> I'm not sure this exact report can be done, esp. the blank cell for total
>> price.  But TABULATE code that gets close to this would be useful.
>>
>> Next, how can I do the same report using PROC REPORT?
>
>PROC REPORT is probably closing to being the right tool for the job.
>
>> In particular the
>> Total row and column:
>>
>> * I know I can easily compute the total column as quantity * price.  I
>> assume REPORT does not have the TABULATE equivalent of all="Total Col"?
>> Instead, it must be computed?
>
>Correct, but since you didn't want the equivalent of tabulate, because
>tabulate doesn't do what you want, that's not necessarily a problem.
>
>> * Can I use the BREAK and SUMMARIZE functionality in REPORT to generate
the
>> Total Row, leaving the cell for Total Price as blank?
>
>Yes, along with compute blocks.
>
>proc report data=test missing nofs box nocenter;
>    column name quantity price total;
>    define name / group;
>    define quantity / sum;
>    define price / group;
>    define total / computed;
>    rbreak after / summarize ol;
>    compute total;
>       if _break_ = '_RBREAK_' then
>          do;
>          name = 'Total';
>          total = gtotal;
>          end;
>       else
>          do;
>          total = quantity.sum * price;
>          gtotal + total;
>          end;
>    endcomp;
>    compute name;
>       if _break_ = '_RBREAK_' then
>          name = 'Total';
>    endcomp;
>run;
>
>prints
>
>   ------------------------------------------
>   |name         quantity   price      total|
>   |----------------------------------------|
>   |WidgetA   |         6|    $10|        60|
>   |----------+----------+-------+----------|
>   |WidgetB   |         9|    $20|       180|
>   |----------+----------+-------+----------|
>   |WidgetC   |         6|    $30|       180|
>   |==========+==========+=======+==========|
>   |Total     |        21|       |       420|
>   ------------------------------------------
>
>which is close to what you want.  I'd leave out the BOX option.