turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to compute a moving average within a by group?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

a week ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

a week ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

a week ago

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

a week ago - last edited a week ago

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

a week ago

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

a week ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

a week ago

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Friday

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

Friday

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Friday

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

Friday

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

Friday

This may be what you want:

proc means data=testfall2016_2;

class Wgt300399;

var price;**weight avgwgt;**

output out = want mean= weighted_avg;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Friday

No, sorry. Still the straight mean.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

Friday

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

Friday

What's best way to send you the data?