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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.