## Sum vertical

Frequent Contributor
Posts: 87

# Sum vertical

Thanks! How do I get the average instead of sum?

Product                  Sales            Wanted_sales_average

Amer                        100                   100

Amer                        100                   200

Amer                          50                   83.33

Amer                        100                   87.5

Amer                         50                    80

Pac                          90                      90

Pac                         110                     100

Pac                          70                       90

ctw                          50                       50

ctw                           80                      65

ctw                           70                       66.66

ctw                           100                     75

Contributor
Posts: 66

## Re: Sum vertical

This should work nicely.

``````DATA have;
length Product \$10 Sales 8;
input Product \$ Sales;
datalines;
Amer                        100
Amer                        100
Amer                         50
Amer                        100
Amer                         50
Pac                          90
Pac                         110
Pac                          70
ctw                          50
ctw                          80
ctw                          70
ctw                         100
;
RUN;

/* this assumes that your data is already sorted by "Product" */
DATA want;
SET have;
BY Product;

FORMAT Wanted_Sales_Average 10.2;

IF first.Product THEN DO;
i = 1;
Running_Sales_Sum = Sales;
END;
ELSE DO;
i + 1;
Running_Sales_Sum + Sales;
END;

Wanted_Sales_Average = Running_Sales_Sum/i;

/* DROP i Running_Sales_Sum; */
RUN;``````

Contributor
Posts: 66

## Re: Sum vertical

Here is the PROC SQL version that accomplishes the same task. Enjoy

``````DATA have;
length Product \$10 Sales 8;

/* need counter variable because PROC SQL cannot use "_N_": */
seq_num = MONOTONIC();

input Product \$ Sales;
datalines;
Amer                        100
Amer                        100
Amer                         50
Amer                        100
Amer                         50
Pac                          90
Pac                         110
Pac                          70
ctw                          50
ctw                          80
ctw                          70
ctw                         100
;
RUN;

PROC SQL;
create table want2 as
select Product
, Sales
, (select sum(a.Sales)/count(a.Sales)
from have as a
where a.seq_num <= b.seq_num
and a.Product = b.Product) format=comma10.2 as Wanted_Sales_Average
from have as b;
QUIT;``````
Valued Guide
Posts: 765

## Re: Sum vertical

[ Edited ]

Hi, another data step solution using a DOW ...

data have;
input product :\$4. sales @@;
datalines;
Amer 100 Amer 100 Amer 50 Amer 100
Amer 50 Pac 90 Pac 110 Pac 70
ctw 50 ctw 80 ctw 70 ctw 100
;

data want;
tot = 0;
do j=1 by 1 until (last.product);
set have;
by product;
tot+sales;
avg = tot /j;
output;
end;
drop j tot;
run;

Discussion stats
• 3 replies
• 176 views
• 0 likes
• 3 in conversation