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

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2018 03:06 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-14-2018 03:11 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-14-2018 08:56 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-14-2018 10:04 PM - edited 02-14-2018 10:13 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-15-2018 09:24 AM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-15-2018 10:47 AM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-15-2018 10:56 AM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-16-2018 06:55 AM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-16-2018 11:01 AM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-16-2018 12:27 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-16-2018 12:31 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Mike15

02-16-2018 01:21 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-16-2018 01:48 PM

No, sorry. Still the straight mean.

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

Posted in reply to Mike15

02-16-2018 03:13 PM

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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-16-2018 03:27 PM

What's best way to send you the data?