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
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;
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!
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)
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.