Date:         Fri, 15 Jan 2010 10:08:29 -0500
Reply-To:     Michael Bryce Herrington <mherrin@G.CLEMSON.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Bryce Herrington <mherrin@G.CLEMSON.EDU>
Subject:      sql help / edit values for same item on different rows
Content-Type: text/plain; charset=ISO-8859-1

Hey,

I sent this out last night but thought I should send it again now that
everyone is back at work.  This is a follow up to a question I had
yesterday.  Thanks for your help.

Now if I have a set like below and I only want to keep the maximum 'worth'
for each 'item' in each 'month' and set the other equal to zero, how would I
do this?  I have a set like the data set "sample" below and want it to be
like the set "final".  For 'month'=2 I have set the 'worth' equal to the
same value for the same 'item', but I only want to count this once.  I have
this situation as well.  I was thinking something like

select distinct a.*, if a.worth=max(a.worth,b.worth) then a.worth as
a.newworth
else 0 as a.newworth
when a.month=b.month, a.item=b.item
from sample a, sample b

But I do not know the syntax and commands in sql to do this.

You could also do this by taking the 'worth' value from the entry for 'item'
with the lowest 'rank' value, 1 being the best, and putting it into the
'newworth' variable.  In the end I want to sum 'newworth', but I do not want
to count the duplicate 'item' entries twice.  Thanks again.

data sample;
input month cost item totalcost rank worth;
datalines;
1 51 1 67 1 100
1 35 2 35 2 .
1 21 3 21 3 .
1 16 1 67 4 85
1 48 4 48 5 50
1 35 5 35 6 .
2 76 2 100 2 123
2 37 1 37 1 70
2 46 3 95 5 .
2 24 2 100 4 123
2 28 4 28 3 .
2 67 5 67 7 .
2 49 3 95 6 .
;
run;


data final;
input month cost item totalcost rank worth newworth;
datalines;
1 51 1 67 1 100 100
1 35 2 35 2 . .
1 21 3 21 3 . .
1 16 1 67 4 85 0
1 48 4 48 5 50 50
1 35 5 35 6 . .
2 76 2 100 2 123 123
2 37 1 37 1 70 70
2 46 3 95 5 . .
2 24 2 100 4 123 0
2 28 4 28 3 . .
2 67 5 67 7 . .
2 49 3 95 6 . .
;
run;

--
Bryce Herrington
Clemson University
mherrin@g.clemson.edu
(863) 258-4758