sas, Grand total in PROC SQL

comp.soft-sys.sas - The SAS statistics package.


sas >> Grand total in PROC SQL

by irinfigvam » Sat, 07 Jun 2008 02:29:18 GMT

I am calculating sum of variables grouped by region. And I wonder how to create a grandtotal for each of variables?
 
For example:
 
              SUMTOT_pt     sumtot_cov
***************************************
MIDWEST       1,000,000      70,000,000
WEST          2,000,000      60,000,000
NORTHHEAST    3,000,000      50,000,000
ETC           4,000,000      10,000,000
**********************************8*****
Grandtot      10.000,000    190,000,000
 
 
Proc SQL ;
Create Table PR As
Select region,sum(patients) as sumtot_pt,
              sum(covered)  as sumtot_cov,
From PR_all
Group By region;
Quit ;
 
How should I adjust my code?  
Thank you in advance,
 
Irin

Top

sas >> Grand total in PROC SQL

by sashole » Sat, 07 Jun 2008 03:05:02 GMT


Irin,

Just drop the

GROUP BY REGION

clause. And do not forget to select ONLY the sums, else SAS will merge
them back with the rest of the table rows. Maybe that is what you want but
then you are not going to get the sums by region. If you want both, then
run the query first time with GROUP BY and one more time without it. Akin
to:

create table whatineed
select *, sum(n_col1) as total_col1, sum(n_col2) as total_col2
from
(select key, sum( col1) as region_col1, sum(col2 ) as region_col2
from whatihave group by key)
;

And an unrelated thing: Could you please somehow rid your posts of what
what surfaces here as  ? It makes your posts very difficult to read.

Kind regards
------------
Paul Dorfman
Jax, FL
------------



create a grandtotal for each of variables?
bsp; SUMTOT_pt     sumtot_cov
1,000,000      70,000,000
2,000,000      60,000,000
50,000,000
4,000,000      10,000,000
190,000,000
bsp; sum(covered)  as sumtot_cov,


Top

sas >> Grand total in PROC SQL

by mlhoward » Sat, 07 Jun 2008 03:39:53 GMT

Irin,

Here's what your code looks like below- please fix! I pulled it into =
Word and got rid of the symbols:

I am calculating sum of variables grouped by region. And I wonder how to =
create a grandtotal for each of variables?
For example:

SUMTOT_pt sumtot_cov
***************************************
MIDWEST 1,000,000 70,000,000
WEST 2,000,000 60,000,000
NORTHHEAST 3,000,000 50,000,000
ETC 4,000,000 10,000,000
**********************************8*****
Grandtot 10.000,000 190,000,000
=20
=20
Proc SQL ;
Create Table PR As
Select region,sum(patients) as sumtot_pt,
sum(covered) as sumtot_cov,
From PR_all
Group By region;
Quit ;
=20
How should I adjust my code? =20
Thank you in advance,
=20
Irin





As to how to get a grand total, it really isn't normal to do this in =
Proc SQL; you should use Proc REPORT instead, which can do this easily.



-Mary

----- Original Message -----=20
From: Irin later=20
To: XXXX@XXXXX.COM =20
Sent: Friday, June 06, 2008 1:29 PM
Subject: Grand total in PROC SQL


I am calculating sum of variables grouped by region. And I wonder how =
to create a grandtotal for each of variables?
 
For example:
 
=
            &=
nbsp; SUMTOT_pt     sumtot_cov
***************************************
MIDWEST       =
1,000,000      70,000,000
WEST          =
2,000,000      60,000,000
NORTHHEAST    3,000,000      =
50,000,000
ETC           =
4,000,000      10,000,000
**********************************8*****
Grandtot      10.000,000    =
190,000,000
 
 
Proc SQL ;
Create Table PR As
Select region,sum(patients) as sumtot_pt,
=
            &=
nbsp; sum(covered)  as sumtot_cov,
From PR_all
Group By region;
Quit ;
 
How should I adjust my code?  
Thank you in advance,
 
Irin

Top

Grand total in PROC SQL

by Mterjeson » Sat, 07 Jun 2008 03:55:36 GMT

Hi,

Here is one approach:

The SQL aggregation functions without a GROUP BY
will give you grand totals. Then you append with
the UNION.

You can then apply cosmetics as you wish for the
final output, the highbit-space(hex=A0,decimal=160)
is merely one way to force the grandtotal line to
the bottom of the automatic SQL sorted output.


data PR_all;
input Region $10. patients covered;
cards;
MIDWEST 500000 35000000
MIDWEST 500000 35000000
WEST 2000000 60000000
NORTHEAST 3000000 50000000
ETC 4000000 10000000
;
run;

Proc SQL;
Create Table PR As
Select region,sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
Group By region
union
select byte(160)||'GRANDTOTAL',
sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
;
Quit;





Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst
Investment Management & Research
Russell Investments


Russell Investments
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of
Irin later
Sent: Friday, June 06, 2008 11:29 AM
To: XXXX@XXXXX.COM
Subject: Grand total in PROC SQL

I am calculating sum of variables grouped by region. And I wonder how to
create a grandtotal for each of variables?
 
For example:
 
            
  SUMTOT_pt     sumtot_cov
***************************************
MIDWEST      
1,000,000      70,000,000
WEST         
2,000,000      60,000,000
NORTHHEAST    3,000,000     
50,000,000
ETC          
4,000,000      10,000,000
**********************************8*****
Grandtot      10.000,000   
190,000,000
 
 
Proc SQL ;
Create Table PR As
Select region,sum(patients) as sumtot_pt,
            
  sum(covered)  as sumtot_cov,
From PR_all
Group By region;
Quit ;
 
How should I adjust my code?  
Thank you in advance,
 
Irin

Top

Grand total in PROC SQL

by hs AT dc-sug DOT org » Sat, 07 Jun 2008 04:51:37 GMT

n Fri, 6 Jun 2008 12:55:36 -0700, Terjeson, Mark < XXXX@XXXXX.COM > wrote:


Do you really want that non-printing character prepended to 'GRANDTOTAL'? It
may be trouble later. An alternative is:

Proc SQL;
Create Table PR As
Select region,sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
Group By region
union
select 'GRANDTOTAL',
sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
order by region='GRANDTOTAL'
;
Quit;

The expression in the ORDER BY clause is false(0) for the regional subtotals
and true(1) for the overall total. Yet another approach is to create a
_TYPE_ column a la PROC MEANS/SUMMARY.


Top

Grand total in PROC SQL

by Mterjeson » Sat, 07 Jun 2008 05:30:14 GMT

i,

Thanks Howard, I do like your ORDER BY better.

I tried to suggest that the author will have
to adjust the cosmetics to the desired end
results. Even with both of us provide a close
simulation of the layout via SQL, the author
looked like they had a much more final set of
cosmetics to shoot for.

Only commenting here on one miniscule aspect:
"printable characters" has become an industry-
standard back from the 1960ish's as we know where
the ASCII character set byte values 32 through 126
have become known as "printable characters". And
the other 0-255 values such as 0-31,127-255 have
become known as "non-printable characters". Don't
forget that ALL 0-255 values are actually printable
characters. Some devices may intercept the byte
and perform a selected routine and not print the
byte, but the space character in both registers
(excuse the musical analogy) will print just fine
as a space character. e.g. hex=20 and hex=A0 or
byte(32) and byte(160) and they can be handy to
sort differently if someone chooses to use them,
it's just another character a person can choose
to use. It may seem unorthodox for those not
comfortable using and understanding what all the
256 character values are and what their behaviors
might be, but it is a usable character.

So, you are still right to ask if it is a straight
forward design they may or maynot want to use, and
I am glad you provided an slick alternative. But it
is indeed a character that does print. :o)

PS: I do like your ORDER BY much, much better.
So thanks for tossing it into the ring.

:o)
Mark




-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of
Howard Schreier <hs AT dc-sug DOT org>
Sent: Friday, June 06, 2008 1:52 PM
To: XXXX@XXXXX.COM
Subject: Re: Grand total in PROC SQL

On Fri, 6 Jun 2008 12:55:36 -0700, Terjeson, Mark
< XXXX@XXXXX.COM > wrote:


Do you really want that non-printing character prepended to
'GRANDTOTAL'? It
may be trouble later. An alternative is:

Proc SQL;
Create Table PR As
Select region,sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
Group By region
union
select 'GRANDTOTAL',
sum(patients) format=comma15. as sumtot_pt,
sum(covered) format=comma15. as sumtot_cov
From PR_all
order by region='GRANDTOTAL'
;
Quit;

The expression in the ORDER BY clause is false(0) for the regional
subtotals
and true(1) for the overall total. Yet another approach is to create a
_TYPE_ column a la PROC MEANS/SUMMARY.

to
;
;

Top
Similar Threads

1. Grand Total by Proc Report

2. i want grand total in proc freq ; 

hi sas experts,
i want grand total in this program
data medi ;
input sid  visit dos $ nopts;
datalines;
100 1 0.05mg 20
101 1 0.05mg 20
102 1 0.05mg 20
103 1 0.05mg 20
100 2 0.10mg 25
101 2 0.10mg 24
102 2 0.10mg 25
103 2 0.10mg 29
100 3 0.15mg 30
101 3 0.15mg 40
102 3 0.15mg 44
103 3 0.15mg 55
;
proc freq data=medi;
table dos visit/nocum;
weight nopts ;
run;

3. Proc Report grand total problem

4. sorting within groups by grand total 

I'm having trouble getting an output in a specific order.

I have some 30 groups with 50 to 60 different types withing each
group, each with a score associated with it.
The data would look something like this (but in  thousands)

input group type score
fruit mango 10
fruit banana 12
vegie carrot 22
vegie lettuce 55
vegie beet 4
dairy cow 1
dairy goat 2
;

I would like to sort based on the on the highest total for group and
then have the types within group ordered in descending order.

something like
group type grand_total
vegie lettuce 55 77
vegie carrot 22 77
fruit banana 12 22
.
.
.


sql or base SAS are fine by me!

TIA
B.

5. How do I get a Grand total

6. The Grand Total Sum... 

Hey.

I would like to sum all observations and all variables into one
figure. I have a table with an unknown amount of variables and unknown
variable_names and an unknown number of observations.

I would like the total sum of all variables and all observations in
this table (they are all numeric, and they all contain a number).

I have tried different approaches with proc means, but nothing seems
to help - hope you people out there can.

(sample code woudl be appreaciated).

7. Paradox: SUM in PROC SQL returns negative total for positive

8. Paradox: SUM in PROC SQL returns negative total for positive numbers 

How is this possible?  I run the following code:

proc sql ;
   select sum(a='1') as Total
   from data ;
quit ;

and get a correct total but with a NEGATIVE sign?  How can a test for
a condition which yields a 0 or a 1 sum to a negative number?

Data source is a MS Access data table where numbers are all stored as
text.

If I modify the syntax just a bit:

proc sql ;
   select sum(strip(a)='1') as Total
   from data ;
quit ;

I get the same number but this time positive.  How does STRIP function
fix this paradox?