topic Sum vertical in SAS Programming

Sum vertical

helloSAS — Tue, 15 Sep 2015 02:08:14 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 vertical

hbi — Wed, 16 Sep 2015 16:58:18 GMT

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;

Re: Sum vertical

hbi — Wed, 16 Sep 2015 17:14:22 GMT

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;

Re: Sum vertical

MikeZdeb — Wed, 16 Sep 2015 17:58:29 GMT

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;