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 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;
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.