DATA Step, Macro, Functions and more

Sum vertically

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Sum vertically

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


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,654

Re: Sum vertically

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;
PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

Re: Sum vertically

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;
Thanks,
Jag
Frequent Contributor
Posts: 87

Re: Sum vertically

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)

Frequent Contributor
Posts: 87

Re: Sum vertically

how do I get average instead of sum in the same situation?
Frequent Contributor
Posts: 87

Re: Sum vertically

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
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,654

Re: Sum vertically

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;
PG
Respected Advisor
Posts: 4,654

Re: Sum vertically

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;
PG
Frequent Contributor
Posts: 87

Re: Sum vertically

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

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 369 views
  • 0 likes
  • 3 in conversation