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






Reeza
Super User

If your weight is Lot Weight:

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

 

Use LotWeight in the WEIGHT statement.

WEIGHT is for the weight variable. 

 

PS. Please post your code using the { i } icon in the editor AND make sure it runs without error first. 

 

Spoiler

@Mike15 wrote:
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;







 

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

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=3; else Wgt300399=0;
if avgwgt=. then Wgt300399=.;

if (avgwgt ge 400) AND (avgwgt le 499) then Wgt400499=4; else Wgt400499=0;
if avgwgt=. then Wgt400499=.;

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

Run;

data TestFall2016_3;
set TestFall2016_2;

WtdPrice=(sum(lotwgt*price))/sum (Lotwgt);

proc means data=testfall2016_3;
class Wgt300399;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;

proc means data=testfall2016_3;
class Wgt400499;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;


proc means data=testfall2016_3;
class Wgt700799;
var Price;
weight WtdPrice;
output out = want mean= weighted_avg;
run;


proc print data=testfall2016_3;
run;}

This code runs. The weighted average price for 300399, 400499 and 700799 is 1.1597, 1.2472, and 1.0951, respectively.




Reeza
Super User

I don't think that's correct.

 

WtdPrice=(sum(lotwgt*price))/sum (Lotwgt); <- this doesn't do what you think it's doing. 

 

proc means data=testfall2016_3;
class Wgt700799;
var Price;
weight LotWgt;
output out = want mean= weighted_avg;
run;

 

You should have all the tools you need to put this together now.  Good Luck. 

 

 

Mike15
Calcite | Level 5
You've been very patient. Thank you so much. It worked.


Reeza
Super User
PS Your description doesn't sound like a moving average, just a weighted average. Or is it a combination of both?
Mike15
Calcite | Level 5
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)


Reeza
Super User

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;

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
  • 1016 views
  • 1 like
  • 2 in conversation