topic Re: How to compute a moving average within a by group? in SAS Programming

How to compute a moving average within a by group?

Mike15 — Wed, 14 Feb 2018 20:06:59 GMT

I have data sorted into 100 lb weight groups. Within each group I would like to compute a weighted average price. The formula is

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

How do I compute this value for each weight group. Thank you.

Re: How to compute a moving average within a by group?

Reeza — Wed, 14 Feb 2018 20:11:06 GMT

PROC MEANS will do this, use the WEIGHT variable to specify the weights and CLASS to specify the groupings.
If you need more help post your code.

@Mike15 wrote:

I have data sorted into 100 lb weight groups. Within each group I would like to compute a weighted average price. The formula is

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

How do I compute this value for each weight group. Thank you.

Re: How to compute a moving average within a by group?

Reeza — Wed, 14 Feb 2018 20:13:00 GMT

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

Re: How to compute a moving average within a by group?

Mike15 — Thu, 15 Feb 2018 01:02:55 GMT

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)

Re: How to compute a moving average within a by group?

Reeza — Thu, 15 Feb 2018 01:06:59 GMT

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;

Re: How to compute a moving average within a by group?

Mike15 — Thu, 15 Feb 2018 01:56:55 GMT

Here's my code. I'm still not following your advice. Mike15

PROC IMPORT OUT= WORK.FEEDERCOMBINED
DATAFILE= "C:\Users\mjb28\Documents\Research\ExpandingBeefPr
odAndMarketing\FeederPrice\Statistics\Recent\Data_COMBINED.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Combined$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

data feedercombined_1;
set feedercombined;

if color=5 then delete;
if color=6 then delete;
if color=7 then delete;
if color=8 then delete;
if sex=2 then delete;
if sex=3 then delete;
if sex=4 then delete;
if sex=5 then delete;
if sex = 6 then delete;
if sex = 7 then delete;
if sex = 8 then delete;
if sex = 9 then delete;

If PreCond=0 then delete;
Run;

data feedercombined_2;
set feedercombined_1;

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 400) AND (avgwgt le 499) then Wgt400499=1; else Wgt400499=0;
if avgwgt=. then Wgt400499=.;

if (avgwgt ge 500) AND (avgwgt le 599) then Wgt500599=1; else Wgt500599=0;
if avgwgt=. then Wgt500599=.;

if (avgwgt ge 600) AND (avgwgt le 699) then Wgt600699=1; else Wgt600699=0;
if avgwgt=. then Wgt600699=.;

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

avgwgt=lotwgt/nohead;

Run;

Proc print Data=feedercombined_2;
Run;

Re: How to compute a moving average within a by group?

Reeza — Thu, 15 Feb 2018 03:13:52 GMT

Did you run the code? Which parts don't you understand?

PROC MEANS prepares summary statistics, so it will summarize your data and apply the weights to the summary.

Regarding your code (data feedercombined_1) you can simplify it using IN. These two lines will replace your block of IF regarding color and sex.

if color in (5:8) then delete;
if sex in (2:9) then delete;

Or replace both with:

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

Re: How to compute a moving average within a by group?

Mike15 — Thu, 15 Feb 2018 14:24:55 GMT

I'm doing something wrong, b/c now I get the message

NOTE: There were 2742 observations read from the data set WORK.FEEDERCOMBINED_1.

NOTE: The data set WORK.FEEDERCOMBINED_2 has 0 observations and 25 variables.

PROC IMPORT OUT= WORK.FEEDERCOMBINED
DATAFILE= "C:\Users\mjb28\Documents\Research\ExpandingBeefPr
odAndMarketing\FeederPrice\Statistics\Recent\Data_COMBINED.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Combined$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

data feedercombined_1;
set feedercombined;

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

Run;

Proc print data=feedercombined_1;

data feedercombined_2;
set feedercombined_1;

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 400) AND (avgwgt le 499) then Wgt400499=1; else Wgt400499=0;
if avgwgt=. then Wgt400499=.;

if (avgwgt ge 500) AND (avgwgt le 599) then Wgt500599=1; else Wgt500599=0;
if avgwgt=. then Wgt500599=.;

if (avgwgt ge 600) AND (avgwgt le 699) then Wgt600699=1; else Wgt600699=0;
if avgwgt=. then Wgt600699=.;

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

avgwgt=lotwgt/nohead;
Run;

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

Proc print Data=feedercombined_2;
Run;

Re: How to compute a moving average within a by group?

Reeza — Thu, 15 Feb 2018 15:47:09 GMT

That's happening in the next step which doesn't change anything in the code I suggested. The error here is in the logic not aligning with the data or your expectations of whats in the data, not the code. The code is syntaxtically correct.

I would start by removing the statements in the data step that creates FeederCombined_2 and add them back one by one to see which one is causing the issue or unexpected behaviour.

Re: How to compute a moving average within a by group?

Reeza — Thu, 15 Feb 2018 15:56:52 GMT

Also, this can't be correct:

proc means data=feedercombined_2;
class avgwgt; <- this is your grouping variable, ie I want analysis by each sex or region;
var price;
weight avgwgt; <- this is the weight variable, it should not be the same as the CLASS variable;
output out = want mean= weighted_avg;
run;

Re: How to compute a moving average within a by group?

Mike15 — Fri, 16 Feb 2018 11:55:55 GMT

Just to confirm. The following code creates a weighted average within each weight class. This is computed as ?(lot wgt x price) ÷ ?(lot wgt)?
proc means data=feedercombined_2;
class Wgt300399;
var price;
weight Wgt300399;
output out = want mean= weighted_avg;
run;

proc means data=feedercombined_2;
class Wgt400499;
var price;
weight Wgt400499;
output out = want mean= weighted_avg;
run;

proc means data=feedercombined_2;
class Wgt500599;
var price;
weight Wgt500599;
output out = want mean= weighted_avg;

proc means data=feedercombined_2;
class Wgt600699;
var price;
weight Wgt600699;
output out = want mean= weighted_avg;

proc means data=feedercombined_2;
class Wgt700799;
var price;
weight Wgt700799;
output out = want mean= weighted_avg;

run;

Re: How to compute a moving average within a by group?

Reeza — Fri, 16 Feb 2018 16:01:02 GMT

Just to confirm. The following code creates a weighted average within each weight class. This is computed as ?(lot wgt x price) ÷ ?(lot wgt)?

I don't think that's correct. Please see my comments in my previous post.

Re: How to compute a moving average within a by group?

Mike15 — Fri, 16 Feb 2018 17:27:55 GMT

So I took a subset of the data and calculated the weighted average to compare to SAS output. The code runs, but the SAS ouput is the straight mean of price not the weighted average of price. For example within one class (300399) there are 3 lots of cattle; each with different weights and price per lot. I want to know the total weighted average price.

PROC IMPORT OUT= WORK.TestFall2016
DATAFILE= "C:\Users\mjb28\Documents\Research\ExpandingBeefPr
odAndMarketing\FeederPrice\Statistics\Recent\TestFall2016.xlsx"
DBMS=excel REPLACE;
GETNAMES=yes;

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 400) AND (avgwgt le 499) then Wgt400499=1; else Wgt400499=0;
if avgwgt=. then Wgt400499=.;

if (avgwgt ge 500) AND (avgwgt le 599) then Wgt500599=1; else Wgt500599=0;
if avgwgt=. then Wgt500599=.;

if (avgwgt ge 600) AND (avgwgt le 699) then Wgt600699=1; else Wgt600699=0;
if avgwgt=. then Wgt600699=.;

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 Wgt300399;
output out = want mean= weighted_avg;
run;

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

proc means data=testfall2016_2;
class Wgt500599;
var price;
weight Wgt500599;
output out = want mean= weighted_avg;

proc means data=testfall2016_2;
class Wgt600699;
var price;
weight Wgt600699;
output out = want mean= weighted_avg;

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

run;

Re: How to compute a moving average within a by group?

Reeza — Fri, 16 Feb 2018 17:31:10 GMT

Your proc means is incorrect, it doesn't make sense to have the CLASS and WEIGHT be the same. Maybe the third times the charm.

Re: How to compute a moving average within a by group?

Reeza — Fri, 16 Feb 2018 18:21:46 GMT

This may be what you want:

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

Re: How to compute a moving average within a by group?

Mike15 — Fri, 16 Feb 2018 18:48:55 GMT

No, sorry. Still the straight mean.

Re: How to compute a moving average within a by group?

Reeza — Fri, 16 Feb 2018 20:13:04 GMT

Then you need to post sample data now.

It does not need to be your real data, but the structure and type should be the same.

@Mike15 wrote:
No, sorry. Still the straight mean.

Re: How to compute a moving average within a by group?

Mike15 — Fri, 16 Feb 2018 20:27:55 GMT

What's best way to send you the data?

Re: How to compute a moving average within a by group?

Reeza — Fri, 16 Feb 2018 20:36:54 GMT

Put it here

Re: How to compute a moving average within a by group?

Mike15 — Sat, 17 Feb 2018 10:07:55 GMT

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;