Date: Thu, 15 Sep 2005 10:54:45 -0400
Reply-To: Talbot Michael Katz <topkatz@MSN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Talbot Michael Katz <topkatz@MSN.COM>
Subject: Re: need to calculate median not sure how to do it
Hi, Greg.
I'm going to assume that, for every line in a group defined by the same
conum and linenum that you want the mediancost for the group, e.g.
conum supplier LINENum ITEM UnitPRICE cost mediancost
836 600 1 6845 5000 $5,000.00 4500
836 620 1 6845 2500 $2,500.00 4500
836 640 1 6845 500 $500.00 4500
836 740 1 6845 5000 $5,000.00 4500
836 770 1 6845 7340.43 $7,340.43 4500
836 830 1 6845 4000 $4,000.00 4500
836 600 2 7039 1500 $9,000.00 9000
836 620 2 7039 1200 $7,200.00 9000
836 640 2 7039 1800 $10,800.00 9000
836 740 2 7039 1500 $9,000.00 9000
836 770 2 7039 2808.68 $16,852.08 9000
836 830 2 7039 1500 $9,000.00 9000
836 600 3 3285 1 $1.00 1
836 620 3 3285 1 $1.00 1
836 640 3 3285 1 $1.00 1
836 740 3 3285 1 $1.00 1
836 770 3 3285 1 $1.00 1
836 830 3 3285 1 $1.00 1
836 600 4 3289 1 $1.00 1
836 620 4 3289 1 $1.00 1
836 640 4 3289 1 $1.00 1
836 740 4 3289 1 $1.00 1
836 770 4 3289 1 $1.00 1
836 830 4 3289 1 $1.00 1
836 600 5 1001 40000 $40,000.00 40000
836 620 5 1001 39500 $39,500.00 40000
836 640 5 1001 38000 $38,000.00 40000
836 740 5 1001 40000 $40,000.00 40000
836 770 5 1001 47357 $47,357.00 40000
836 830 5 1001 42000 $42,000.00 40000
(You mentioned supplier, too, but the supplier never repeats within conum
and linenum in your example.)
If you're using SAS 9, then you have the median function, and you should
be able to use the following partially-tested SQL:
proc sql ;
create table tesmed2 as
select t.*, m.mediancost
from tesmed0 t
inner join
(select conum, linenum,
median(cost) as mediancost
from tesmed0
group by conum, linenum)
m
on t.conum = m.conum and
t.linenum = m.linenum
order by t.conum, t.linenum, t.supplier
;
quit ;
Otherwise, you can use a two-step solution:
proc summary data = tesmed0 nway ;
class conum linenum ;
var cost ;
output out = tesmedo median=mediancost
;
run ;
proc sql ;
create table tesmed3 as
select t.*, m.mediancost
from tesmed0 t
inner join
tesmedo m
on t.conum = m.conum and
t.linenum = m.linenum
order by t.conum, t.linenum, t.supplier
;
quit ;
Hope this helps!
-- TMK --
"The Macro Klutz"
On Thu, 15 Sep 2005 07:01:21 -0700, Greg Curson <gscsrc@HOTMAIL.COM> wrote:
>I have more fields in my dataset than what is shown below but
>I need to code for the median price of cost and I need to put it into
>the dataset and would like to create a field called mediancost
>I want to put it into the dataset because I have more I have to
>do with it than just find the median cost
>
>I need the median of cost by the LINENum, supplier and conum
>see LINENum will repeat when there is a new conum
>
>in the searches I have found many ways to code for median but I can
>not figure out how to use them with my problem.
>
>can someone help me?
>
>any help would be appreciated!
>Thanks,
>Greg
>
>
>
>conum supplier LINENum ITEM UnitPRICE cost mediancost
>
>836 600 1 6845 5000 $5,000.00
>836 620 1 6845 2500 $2,500.00
>836 640 1 6845 500 $500.00
>836 740 1 6845 5000 $5,000.00
>836 770 1 6845 7340.43 $7,340.43
>836 830 1 6845 4000 $4,000.00
>836 600 2 7039 1500 $9,000.00
>836 620 2 7039 1200 $7,200.00
>836 640 2 7039 1800 $10,800.00
>836 740 2 7039 1500 $9,000.00
>836 770 2 7039 2808.68 $16,852.08
>836 830 2 7039 1500 $9,000.00
>836 600 3 3285 1 $1.00
>836 620 3 3285 1 $1.00
>836 640 3 3285 1 $1.00
>836 740 3 3285 1 $1.00
>836 770 3 3285 1 $1.00
>836 830 3 3285 1 $1.00
>836 600 4 3289 1 $1.00
>836 620 4 3289 1 $1.00
>836 640 4 3289 1 $1.00
>836 740 4 3289 1 $1.00
>836 770 4 3289 1 $1.00
>836 830 4 3289 1 $1.00
>836 600 5 1001 40000 $40,000.00
>836 620 5 1001 39500 $39,500.00
>836 640 5 1001 38000 $38,000.00
>836 740 5 1001 40000 $40,000.00
>836 770 5 1001 47357 $47,357.00
>836 830 5 1001 42000 $42,000.00
| 
