How to compute a moving average within a by group?

Reply
Occasional Contributor
Posts: 11

How to compute a moving average within a by group?

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.

Super User
Posts: 21,530

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

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.


 

Occasional Contributor
Posts: 11

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

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;




Super User
Posts: 21,530

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

[ Edited ]

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;

 

Occasional Contributor
Posts: 11

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

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;






Super User
Posts: 21,530

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

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.

Super User
Posts: 21,530

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

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;

Occasional Contributor
Posts: 11

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

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;




Super User
Posts: 21,530

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

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. 

Occasional Contributor
Posts: 11

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

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;





Super User
Posts: 21,530

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

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. 

Super User
Posts: 21,530

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

This may be what you want:

 

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

Occasional Contributor
Posts: 11

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

No, sorry. Still the straight mean.


Super User
Posts: 21,530

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

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.



 

Occasional Contributor
Posts: 11

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

What's best way to send you the data?


Ask a Question
Discussion stats
  • 23 replies
  • 153 views
  • 1 like
  • 2 in conversation