DATA Step, Macro, Functions and more

Sum vertical

Reply
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 hbi
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;

  sas_sum_vertically.gif

Contributor hbi
Contributor
Posts: 66

Re: Sum vertical

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

 

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;

Ask a Question
Discussion stats
  • 3 replies
  • 162 views
  • 0 likes
  • 3 in conversation