Summing Different Variables Across Different Observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

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.

Thanks for your help.

John


Accepted Solutions
Solution
‎04-06-2014 07:06 PM
Super User
Super User
Posts: 6,495

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

View solution in original post


All Replies
Solution
‎04-06-2014 07:06 PM
Super User
Super User
Posts: 6,495

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 217 views
  • 0 likes
  • 2 in conversation