How to compute a moving average within a by group?

[Reeza]

Put it here

[Mike15]

data Testfall2016;
input Date LotWgt NoHead AvgWt Price;
cards;

10/15/2016 314 1 314 1.13
10/15/2016 317 1 317 1.10
10/15/2016 384 1 384 1.26
10/15/2016 771 2 386 0.94
10/15/2016 2374 6 396 1.22
10/15/2016 399 1 399 1.20
10/15/2016 4239 10 424 1.28
10/15/2016 428 1 428 1.19
10/15/2016 874 2 437 1.22
10/15/2016 2629 6 438 1.20
10/15/2016 911 2 456 1.15
10/15/2016 2401 5 480 1.28
10/15/2016 3400 7 486 1.26
10/15/2016 2026 4 507 1.23
10/15/2016 1030 2 515 1.17
10/15/2016 524 1 524 0.82
10/15/2016 3309 6 552 1.21
10/15/2016 4604 8 576 1.20
10/15/2016 1183 2 592 0.79
10/15/2016 1297 2 649 1.00
10/15/2016 4678 7 668 1.20
10/15/2016 685 1 685 0.99
10/15/2016 743 1 743 0.96
10/15/2016 4706 6 784 1.16
10/15/2016 3149 4 787 1.03
RUN;
Proc print; run;


data TestFall2016_1;
set TestFall2016;


if color in (5:8)
OR sex in (2:9)
OR PreCond=0 then delete;
Run;


data TestFall2016_2;
set TestFall2016_1;

avgwgt=lotwgt/nohead;



If AvgWgt<300 then delete;
If AvgWgt>799 then delete;

if (avgwgt ge 300) AND (avgwgt le 399) then Wgt300399=1; else Wgt300399=0;
if avgwgt=. then Wgt300399=.;

if (avgwgt ge 700) AND (avgwgt le 799) then Wgt700799=1; else Wgt700799=0;
if avgwgt=. then Wgt700799=.;

Proc print data=testfall2016_2;

Run;

proc means data=testfall2016_2;
class Wgt300399;
var price;
weight avgwgt;
output out = want mean= weighted_avg;
run;


proc means data=testfall2016_2;
class Wgt700799;
var price;
weight avgwgt;
output out = want mean= weighted_avg;
run;

run;
proc print data=testfall2016_3;
run;

[Reeza]

If your weight is Lot Weight:

(sum of (Lot weight x price)) / sum of Lot weight

Use LotWeight in the WEIGHT statement.

WEIGHT is for the weight variable. 

PS. Please post your code using the { i } icon in the editor AND make sure it runs without error first. 

Spoiler

---

@Mike15 wrote:
data Testfall2016;
input Date LotWgt NoHead AvgWt Price;
cards;

10/15/2016 314 1 314 1.13
10/15/2016 317 1 317 1.10
10/15/2016 384 1 384 1.26
10/15/2016 771 2 386 0.94
10/15/2016 2374 6 396 1.22
10/15/2016 399 1 399 1.20
10/15/2016 4239 10 424 1.28
10/15/2016 428 1 428 1.19
10/15/2016 874 2 437 1.22
10/15/2016 2629 6 438 1.20
10/15/2016 911 2 456 1.15
10/15/2016 2401 5 480 1.28
10/15/2016 3400 7 486 1.26
10/15/2016 2026 4 507 1.23
10/15/2016 1030 2 515 1.17
10/15/2016 524 1 524 0.82
10/15/2016 3309 6 552 1.21
10/15/2016 4604 8 576 1.20
10/15/2016 1183 2 592 0.79
10/15/2016 1297 2 649 1.00
10/15/2016 4678 7 668 1.20
10/15/2016 685 1 685 0.99
10/15/2016 743 1 743 0.96
10/15/2016 4706 6 784 1.16
10/15/2016 3149 4 787 1.03
RUN;
Proc print; run;


data TestFall2016_1;
set TestFall2016;


if color in (5:8)
OR sex in (2:9)
OR PreCond=0 then delete;
Run;


data TestFall2016_2;
set TestFall2016_1;

avgwgt=lotwgt/nohead;



If AvgWgt<300 then delete;
If AvgWgt>799 then delete;

if (avgwgt ge 300) AND (avgwgt le 399) then Wgt300399=1; else Wgt300399=0;
if avgwgt=. then Wgt300399=.;

if (avgwgt ge 700) AND (avgwgt le 799) then Wgt700799=1; else Wgt700799=0;
if avgwgt=. then Wgt700799=.;

Proc print data=testfall2016_2;

Run;

proc means data=testfall2016_2;
class Wgt300399;
var price;
weight avgwgt;
output out = want mean= weighted_avg;
run;


proc means data=testfall2016_2;
class Wgt700799;
var price;
weight avgwgt;
output out = want mean= weighted_avg;
run;

run;
proc print data=testfall2016_3;
run;

---

[Mike15]

{data Testfall2016;
input Date$ LotWgt NoHead AvgWt Price;
cards;

10/15/2016 314 1 314 1.13
10/15/2016 317 1 317 1.10
10/15/2016 384 1 384 1.26
10/15/2016 771 2 386 0.94
10/15/2016 2374 6 396 1.22
10/15/2016 399 1 399 1.20
10/15/2016 4239 10 424 1.28
10/15/2016 428 1 428 1.19
10/15/2016 874 2 437 1.22
10/15/2016 2629 6 438 1.20
10/15/2016 911 2 456 1.15
10/15/2016 2401 5 480 1.28
10/15/2016 3400 7 486 1.26
10/15/2016 2026 4 507 1.23
10/15/2016 1030 2 515 1.17
10/15/2016 524 1 524 0.82
10/15/2016 3309 6 552 1.21
10/15/2016 4604 8 576 1.20
10/15/2016 1183 2 592 0.79
10/15/2016 1297 2 649 1.00
10/15/2016 4678 7 668 1.20
10/15/2016 685 1 685 0.99
10/15/2016 743 1 743 0.96
10/15/2016 4706 6 784 1.16
10/15/2016 3149 4 787 1.03
RUN;

data TestFall2016_1;
set TestFall2016;


if color in (5:8)
OR sex in (2:9)
OR PreCond=0 then delete;
Run;


data TestFall2016_2;
set TestFall2016_1;

avgwgt=lotwgt/nohead;



If AvgWgt<300 then delete;
If AvgWgt>799 then delete;

if (avgwgt ge 300) AND (avgwgt le 399) then Wgt300399=3; else Wgt300399=0;
if avgwgt=. then Wgt300399=.;

if (avgwgt ge 400) AND (avgwgt le 499) then Wgt400499=4; else Wgt400499=0;
if avgwgt=. then Wgt400499=.;

if (avgwgt ge 700) AND (avgwgt le 799) then Wgt700799=7; else Wgt700799=0;
if avgwgt=. then Wgt700799=.;

Run;

data TestFall2016_3;
set TestFall2016_2;

WtdPrice=(sum(lotwgt*price))/sum (Lotwgt);

proc means data=testfall2016_3;
class Wgt300399;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;

proc means data=testfall2016_3;
class Wgt400499;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;


proc means data=testfall2016_3;
class Wgt700799;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;


proc print data=testfall2016_3;
run;}

This code runs. The weighted average price for 300399, 400499 and 700799 is 1.1597, 1.2472, and 1.0951, respectively.

[Reeza]

I don't think that's correct.

WtdPrice=(sum(lotwgt*price))/sum (Lotwgt); <- this doesn't do what you think it's doing. 

proc means data=testfall2016_3;
class Wgt700799;
var Price;
weight LotWgt;
output out = want mean= weighted_avg;
run;

You should have all the tools you need to put this together now.  Good Luck. 

[Mike15]

You've been very patient. Thank you so much. It worked.

[Reeza]

PS Your description doesn't sound like a moving average, just a weighted average. Or is it a combination of both?

[Mike15]

It's a weighted average, not a moving average. In Excel, I would use "sumproduct" to add up all of the prices. See attachment and below for an example.



A

B

1

Lot weight

Price

2

664

0.89

3

2676

0.83

4

501

0.79

5

566

0.66

6

420

0.74

7

1775

0.91

8

2283

1.07

9





10

Wtd Price

0.89






Formula: =SUMPRODUCT(A2:A8,B2:B8)/SUM(A2:A8)

[Reeza]

Then my first answer was correct. Use PROC MEANS.

Useless example here:

proc means data=sashelp.class;
class sex;
var age;
weight weight;
output out = want mean= weighted_avg;
run;