BookmarkSubscribeRSS Feed
Mike15
Calcite | Level 5

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.

23 REPLIES 23
Reeza
Super User

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.


 

Mike15
Calcite | Level 5
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;




Reeza
Super User

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;

 

Mike15
Calcite | Level 5
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;






Reeza
Super User

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.

Reeza
Super User

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;

Mike15
Calcite | Level 5
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;




Reeza
Super User

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. 

Mike15
Calcite | Level 5
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;





Reeza
Super User

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. 

Reeza
Super User

This may be what you want:

 

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

Mike15
Calcite | Level 5
No, sorry. Still the straight mean.


Reeza
Super User

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.



 

Mike15
Calcite | Level 5
What's best way to send you the data?


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 23 replies
  • 1018 views
  • 1 like
  • 2 in conversation