## Summing Different Variables Across Different Observations

Solved
Frequent Contributor
Posts: 101

# Summing Different Variables Across Different Observations

Hello,

I have a problem that I have been trying to conquer forever and I can't figure it out.

I have data that takes the following form.

ID                           QTR                           TOP                   BOTTOM

001                         2001Q1                         .                         100

001                         2001Q1                        50                         .

001                         2001Q1                        75                         .

001                         2001Q1                         .                         150

...                              .....                                ....

002                         2001Q2                           15                      .

002                         2001Q2                            20                     .

002                         2001Q2                              .                     75

002                         2001Q2                              .                     100

....                              .....                              .....

and I would like to create a sum of all of the TOP and BOTTOM variables by ID and QTR.

I have tried the following code to no avail.

proc sql;

create table mylib.pressure3 as

select *,

sum(TOP,  BOTTOM) as SUM

from mylib.pressure2

group by ID, QTR

order by ID, QTR;

quit;

I should be getting the same value for SUM whenever the ID and QTR are shared, but I am getting a different value for SUM for each observation.  I want a dataset that returns 375 for SUM for the first set of observations and 210 for the second set, etc.

John

Accepted Solutions
Solution
‎04-06-2014 07:06 PM
Super User
Posts: 8,115

## Re: Summing Different Variables Across Different Observations

You need to use two different SUM() functions.

You could use the SAS sum() function to sum the values of TOP and BOTTOM and then use the SQL aggregate function to total those over the group.

proc sql noprint ;

create table want as

select *,sum(sum(top,bottom)) as grandtotal

from have

group by id,qtr

order by id,qtr

;

run;

proc print;

run;

 1 1 2001Q1 75 . 375 2 1 2001Q1 . 100 375 3 1 2001Q1 50 . 375 4 1 2001Q1 . 150 375 5 2 2001Q2 . 75 210 6 2 2001Q2 20 . 210 7 2 2001Q2 15 . 210 8 2 2001Q2 . 100 210

All Replies
Solution
‎04-06-2014 07:06 PM
Super User
Posts: 8,115

## Re: Summing Different Variables Across Different Observations

You need to use two different SUM() functions.

You could use the SAS sum() function to sum the values of TOP and BOTTOM and then use the SQL aggregate function to total those over the group.

proc sql noprint ;

create table want as

select *,sum(sum(top,bottom)) as grandtotal

from have

group by id,qtr

order by id,qtr

;

run;

proc print;

run;

 1 1 2001Q1 75 . 375 2 1 2001Q1 . 100 375 3 1 2001Q1 50 . 375 4 1 2001Q1 . 150 375 5 2 2001Q2 . 75 210 6 2 2001Q2 20 . 210 7 2 2001Q2 15 . 210 8 2 2001Q2 . 100 210
Frequent Contributor
Posts: 101

## Re: Summing Different Variables Across Different Observations

Thank you so much!!!

Really appreciate it.

🔒 This topic is solved and locked.