Doubts on Avg, Max, Min ?

[Cap2010]

hi,

Below is my code,
[COLOR=blue ]What I wanted is each month max, min, avg qty delivered.
What is happening is it gives me data more than once. How can I get as Required Output

dd-mm
Date item qty
1-4 1 10
2-4 1 5
3-4 1 0
4-4 1 15

Required Output
Item Max Min Avg
1 15 5 10
[/color]


Wrong Output Given by below statement
Item Max Min Avg
1 15 5 10
1 15 5 10
1 15 5 10


[bold]
option notes source;
proc sql;
create table work.max as
SELECT
m.Region
, i.Itemid
, Month(i.date) AS mth
, m.Address
, i.Qty
, Max(i.qty) AS Maxqty
, Min(i.qty) AS Minqty
, Avg(i.qty) AS Avgqty
FROM data.masterid as m
INNER JOIN data.itemtrans as i
ON m.item_id = i.item_id
GROUP BY
c.region
, i.item_id
, Month(i.date)
, m.address
, i.Qty
, Year(i.date)
HAVING i.validate="0"
AND i.qty <> 0
AND c.Active="1"
AND Year(i.date)>=2004
ORDER BY c.region, i.Itemid;
quit; [/bold]

 

[klaz2002]

In your GROUP BY clause only leave the itemid in. This way you will group your statistics by the itemid which is what you seem to want.

EX.
....
group by itemid
....

Hope that this helps you.
Klaz