Date: Wed, 10 May 2006 10:54:55 -0700
Reply-To: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: PROC TABULATE vs. PROC REPORT
In-Reply-To: <200605101158.k4AAmhLu006812@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
I don't remember why this works, but it does. Seems like a kludge:
proc report data=test box nowd split="^" missing;
columns _name name2 quantity price total;
define _name / group noprint left width=10 format=widget.;
define name2 / computed format=widget10. width=10;
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 name2;
if _break_ = '_RBREAK_' then
name2 = .Z;
else
name2 = _name;
endcomp;
compute total;
if _break_ = '_RBREAK_' then do;
total = gtotal;
_name = .Z;
end;
else do;
total = quantity.sum * price;
gtotal + total;
end;
endcomp;
run;
on 5/10/2006 4:58 AM Scott Bass said the following:
> 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.
>
| 
