Prev: SAS Enterprise Guide - Documentation
Next: book: Making Data Talk
From: Arthur Tabachneck on 11 Jan 2010 20:59
Lorne,

If they don't match then you will have a problem. Surely someone will look
and add the numbers in your rows and/or columns and expect the totals to
match what they see.

Art
---------
On Mon, 11 Jan 2010 20:31:21 -0500, Lorne Klassen <lk1(a)ROGERS.COM> wrote:

>On Thu, 7 Jan 2010 21:32:24 -0500, Francois van der Walt
><francoisw(a)GJI.COM.AU> wrote:
>
>>On Thu, 7 Jan 2010 21:13:47 -0500, Francois van der Walt
>><francoisw(a)GJI.COM.AU> wrote:
>>
>>Hi Lorne,
>>
>>by changing the table statement you can have:
>>--------------------------------------------------------------
>>| | car |
>>| |---------------------------------------------------|
>>| | Ferrari | Honda | Mercedes | Toyota |
>>| |------------+------------+------------+------------|
>>| | count | count | count | count |
>>| |------------+------------+------------+------------|
>>| | Sum | Sum | Sum | Sum |
>>|--------+------------+------------+------------+------------|
>>|year | | | | |
>>|--------| | | | |
>>|95 | 0.00| 5.00| 5.00| 0.00|
>>|--------+------------+------------+------------+------------|
>>|96 | 0.00| 15.00| 0.00| 0.00|
>>|--------+------------+------------+------------+------------|
>>|97 | 0.00| 5.00| 0.00| 5.00|
>>|--------+------------+------------+------------+------------|
>>|98 | 0.00| 0.00| 0.00| 5.00|
>>|--------+------------+------------+------------+------------|
>>|99 | 0.00| 0.00| 5.00| 5.00|
>>--------------------------------------------------------------
>>
>>Full code:
>>
>>data cars;
>> input @1 car $10. @11 year 5. @19 count 5.;
>>datalines;
>> 50
>> 95 10
>> 96 15
>> 97 10
>> 98 5
>> 99 10
>>Ferrari 0
>>Ferrari 95 0
>>Ferrari 96 0
>>Ferrari 97 0
>>Ferrari 98 0
>>Ferrari 99 0
>>Honda 25
>>Honda 95 5
>>Honda 96 15
>>Honda 97 5
>>Honda 98 0
>>Honda 99 0
>>Mercedes 10
>>Mercedes 95 5
>>Mercedes 96 0
>>Mercedes 97 0
>>Mercedes 98 0
>>Mercedes 99 5
>>Toyota 15
>>Toyota 95 0
>>Toyota 96 0
>>Toyota 97 5
>>Toyota 98 5
>>Toyota 99 5
>>;
>>* remove formchar part for the html output;
>>proc tabulate data=cars formchar(1,2,3,4,5,6,7,8,9,10,11)
>> ='|----|+|---';
>> class car year;
>> var count;
>> table year,car*count*sum/rtspace=10;
>>run;
>
>Francois,
>
>That's close to what I want but is there any way to get the totals from my
>input data set into that report?
>
>The totals I'm referring to are in the records where Car and/or Year are
>blank. I have to use these totals instead of totals that Proc Tabulate
>might calculate.
>
>Thanks
From: Francois van der Walt on 11 Jan 2010 22:06
Dear Lorna,

If you change the table instruction:

table year,car*count*sum/rtspace=10;

To:

Table year all,(car all)*count*sum/rtspace=10;

It will give you the result you expect!


Francois van der Walt

Senior Business Analyst

G J I

'Stay in Front'

DATA - PRINT - MAIL



Please consider the environment before printing this email.


-----Original Message-----
From: Lorne Klassen [mailto:lk1(a)ROGERS.COM]
Sent: Tuesday, 12 January 2010 11:31 AM
To: SAS-L(a)LISTSERV.UGA.EDU; Francois van der Walt
Subject: Re: Create an HTML report from this dataset

On Thu, 7 Jan 2010 21:32:24 -0500, Francois van der Walt
<francoisw(a)GJI.COM.AU> wrote:

>On Thu, 7 Jan 2010 21:13:47 -0500, Francois van der Walt
><francoisw(a)GJI.COM.AU> wrote:
>
>Hi Lorne,
>
>by changing the table statement you can have:
>--------------------------------------------------------------
>| | car |
>| |---------------------------------------------------|
>| | Ferrari | Honda | Mercedes | Toyota |
>| |------------+------------+------------+------------|
>| | count | count | count | count |
>| |------------+------------+------------+------------|
>| | Sum | Sum | Sum | Sum |
>|--------+------------+------------+------------+------------|
>|year | | | | |
>|--------| | | | |
>|95 | 0.00| 5.00| 5.00| 0.00|
>|--------+------------+------------+------------+------------|
>|96 | 0.00| 15.00| 0.00| 0.00|
>|--------+------------+------------+------------+------------|
>|97 | 0.00| 5.00| 0.00| 5.00|
>|--------+------------+------------+------------+------------|
>|98 | 0.00| 0.00| 0.00| 5.00|
>|--------+------------+------------+------------+------------|
>|99 | 0.00| 0.00| 5.00| 5.00|
>--------------------------------------------------------------
>
>Full code:
>
>data cars;
> input @1 car $10. @11 year 5. @19 count 5.; datalines;
> 50
> 95 10
> 96 15
> 97 10
> 98 5
> 99 10
>Ferrari 0
>Ferrari 95 0
>Ferrari 96 0
>Ferrari 97 0
>Ferrari 98 0
>Ferrari 99 0
>Honda 25
>Honda 95 5
>Honda 96 15
>Honda 97 5
>Honda 98 0
>Honda 99 0
>Mercedes 10
>Mercedes 95 5
>Mercedes 96 0
>Mercedes 97 0
>Mercedes 98 0
>Mercedes 99 5
>Toyota 15
>Toyota 95 0
>Toyota 96 0
>Toyota 97 5
>Toyota 98 5
>Toyota 99 5
>;
>* remove formchar part for the html output; proc tabulate data=cars
>formchar(1,2,3,4,5,6,7,8,9,10,11)
> ='|----|+|---';
> class car year;
> var count;
> table year,car*count*sum/rtspace=10;
>run;

Francois,

That's close to what I want but is there any way to get the totals from
my input data set into that report?

The totals I'm referring to are in the records where Car and/or Year are
blank. I have to use these totals instead of totals that Proc Tabulate
might calculate.

Thanks
From: Tom Abernathy on 12 Jan 2010 00:10
The totals are already in your data, but the class variables are
missing (blank) for those observations and PROC TABULATE is ignoring
them. So you need to add the MISSING option to the PROC TABULATE
statement to allow missing values of class variables.
You also might want to create a format that will display the blank
values of the class variable as something more interesting.
proc format;
value $total ' '='Total' other=[$.] ;
run;


> Francois,
>
> That's close to what I want but is there any way to get the totals from my
> input data set into that report?
>
> The totals I'm referring to are in the records where Car and/or Year are
> blank. I have to use these totals instead of totals that Proc Tabulate
> might calculate.
>
> Thanks- Hide quoted text -
>
> - Show quoted text -

From: Mary on 12 Jan 2010 10:13
Lorne,

Getting the Totals in isn't something you can do by just using Proc Tabulate; what you have to
do is to create a data set with your results that you want and then append the total records
to those results (since they are not totals that come out of Tabulate; that's a different situation
where I usually rename the total line to be something like ZZ. TOTAL to get it to filter to the bottom).

To get your proc tabulate results into a data set, you can do
ods output table=table1;
proc tabulate...

BUT, you aren't going to like the resulting data set!!!! So if you do that then you've got
to manipulate the resulting data set to where you wanted it, or give up on using tabulate at
all and use some other procedure; such as PROC SQL, MEANS, or SUMMARY to get your data into
a data set, such as:

proc sql noprint;
create table summary_table
as select car, year, sum(count) as sum
group by car, year
order by car, year
where year ^=.;
quit;

and then proc transpose to get the columns to be the cars.

proc transpose data=summary_table out=detail_rows;
by year;
var sum;
id car;
run;

Then do a similar thing with your total row and transpose that.

Then put the two together:

data all;
set detail_rows total_rows;
run;

THEN, you can use proc report to get your final report, which you can
put an ODS wrapper around to get xml or html

ods html...;
proc report data=all;
ods html close;

We do a lot of these where total rows really aren't totals (such as univariates), and they are always lots of fun :-)


NOT tested.

-Mary

--- lk1(a)ROGERS.COM wrote:

From: Lorne Klassen <lk1(a)ROGERS.COM>
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Create an HTML report from this dataset
Date: Mon, 11 Jan 2010 20:31:21 -0500

On Thu, 7 Jan 2010 21:32:24 -0500, Francois van der Walt
<francoisw(a)GJI.COM.AU> wrote:

>On Thu, 7 Jan 2010 21:13:47 -0500, Francois van der Walt
><francoisw(a)GJI.COM.AU> wrote:
>
>Hi Lorne,
>
>by changing the table statement you can have:
>--------------------------------------------------------------
>| | car |
>| |---------------------------------------------------|
>| | Ferrari | Honda | Mercedes | Toyota |
>| |------------+------------+------------+------------|
>| | count | count | count | count |
>| |------------+------------+------------+------------|
>| | Sum | Sum | Sum | Sum |
>|--------+------------+------------+------------+------------|
>|year | | | | |
>|--------| | | | |
>|95 | 0.00| 5.00| 5.00| 0.00|
>|--------+------------+------------+------------+------------|
>|96 | 0.00| 15.00| 0.00| 0.00|
>|--------+------------+------------+------------+------------|
>|97 | 0.00| 5.00| 0.00| 5.00|
>|--------+------------+------------+------------+------------|
>|98 | 0.00| 0.00| 0.00| 5.00|
>|--------+------------+------------+------------+------------|
>|99 | 0.00| 0.00| 5.00| 5.00|
>--------------------------------------------------------------
>
>Full code:
>
>data cars;
> input @1 car $10. @11 year 5. @19 count 5.;
>datalines;
> 50
> 95 10
> 96 15
> 97 10
> 98 5
> 99 10
>Ferrari 0
>Ferrari 95 0
>Ferrari 96 0
>Ferrari 97 0
>Ferrari 98 0
>Ferrari 99 0
>Honda 25
>Honda 95 5
>Honda 96 15
>Honda 97 5
>Honda 98 0
>Honda 99 0
>Mercedes 10
>Mercedes 95 5
>Mercedes 96 0
>Mercedes 97 0
>Mercedes 98 0
>Mercedes 99 5
>Toyota 15
>Toyota 95 0
>Toyota 96 0
>Toyota 97 5
>Toyota 98 5
>Toyota 99 5
>;
>* remove formchar part for the html output;
>proc tabulate data=cars formchar(1,2,3,4,5,6,7,8,9,10,11)
> ='|----|+|---';
> class car year;
> var count;
> table year,car*count*sum/rtspace=10;
>run;

Francois,

That's close to what I want but is there any way to get the totals from my
input data set into that report?

The totals I'm referring to are in the records where Car and/or Year are
blank. I have to use these totals instead of totals that Proc Tabulate
might calculate.

Thanks
First  |  Prev  | 
Pages: 1 2
Prev: SAS Enterprise Guide - Documentation
Next: book: Making Data Talk