```Date: Thu, 15 Sep 2005 10:54:45 -0400 Reply-To: Talbot Michael Katz Sender: "SAS(r) Discussion" From: Talbot Michael Katz Subject: Re: need to calculate median not sure how to do it Comments: To: Greg Curson 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 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 ```

Back to: Top of message | Previous page | Main SAS-L page