topic Re: Sum vertically in SAS Programming

Sum vertically

helloSAS — Tue, 15 Sep 2015 00:24:07 GMT

Hi,

I have product and sales in my input data, I want to create the result column as "Wanted_sales_sum". That column is basically sum of sales field within each product group.

Product Sales Wanted_sales_sum

Amer 100 100

Amer 100 200

Amer 50 250

Amer 100 350

Amer 50 400

Pac 90 90

Pac 110 200

Pac 70 270

ctw 50 50

ctw 80 130

ctw 70 200

ctw 100 300

Re: Sum vertically

Jagadishkatam — Tue, 15 Sep 2015 00:55:45 GMT

proc sort data=have;
by product;
run;

data want;
set have;
by product;
retain Wanted_sales_sum;
if first.product then Wanted_sales_sum=sales;
else Wanted_sales_sum+sales;
run;

Re: Sum vertically

helloSAS — Tue, 15 Sep 2015 01:42:29 GMT

Thanks!

How do I do the same for getting average?

Product Sales Wanted_sales_sum

Amer 100 100

Amer 100 100 (=200/2)

Amer 50 83.33 (=250/3)

Amer 100 87.5 (=350/4)

Amer 50 80 (=400/5)

Pac 90 90

Pac 110 100 (=200/2)

Pac 70 90 (=270/3)

ctw 50 50

ctw 80 65 (=130/2)

ctw 70 66.66 (=200/3)

ctw 100 75 (=300/4)

Re: Sum vertically

helloSAS — Tue, 15 Sep 2015 02:05:58 GMT

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

Re: Sum vertically

helloSAS — Tue, 15 Sep 2015 02:10:20 GMT

how do I get average instead of sum in the same situation?

Re: Sum vertically

helloSAS — Tue, 15 Sep 2015 02:14:08 GMT

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

Re: Sum vertically

PGStats — Tue, 15 Sep 2015 02:21:10 GMT

Might be better to keep original Product order with notsorted option. Retain is not required with sum statement variables (they are retained by default).

data want;
set have; by Product notsorted;
if first.Product then Wanted_Sales_Sum = 0;
Wanted_Sales_Sum + Sales;
run;

Re: Sum vertically

PGStats — Tue, 15 Sep 2015 02:25:50 GMT

To get the running mean instead of the sum:

data want;
set have; by Product notsorted;
if first.Product then do;
    Sum = 0;
    n = 0;
    end;
Sum + Sales;
n + 1;
Wanted_Sales_Mean = Sum / n;
drop Sum n;
run;