LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2010, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


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