Floyd,

I think that your original solution is best. I do not see how your present solution can work. Consider an earlier valuetime and a smaller value later in the group. It falls through your test because the time restriction fails. But this means the maxvalue will change and it shouldn't. I do not doubt that the code cannot be fixed, but suggest that the basic idea is wrong for this problem because it is hard to get the code correct and see that it is correct.

IanWhitlock@westat.com

-----Original Message----- From: Nevseta, Floyd G [mailto:Floyd.G.Nevseta@BANKOFAMERICA.COM] Sent: Tuesday, August 27, 2002 4:08 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: How to find MAX value corresponding with time.

The DoW-loop always amazes me with its simplicity and efficiency. I need to train myself to look for ways to implement it in my everday work. However, to provide what the original poster is looking for, which is the earliest occurrence of the max value, then I would modify your code as such:

data max (keep=county maxtime maxvalue) ; do until ( last.county ) ; set table1 ; by county ; /* the earliest occurrence of the greatest value */ if valuetime >= maxtime & value <= maxvalue then continue ; maxtime = valuetime ; maxvalue = value ; end; run;

This is the best solution if the data is already sorted or grouped by county. However, if you must sort, then I'll stand by my original solution :)

Regards, Floyd

-----Original Message----- From: Dorfman, Paul [mailto:Paul.Dorfman@BCBSFL.COM] Sent: Tuesday, August 27, 2002 3:05 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: How to find MAX value corresponding with time.

Vanessa,

The RETAIN is only needed because you are trying to force everything in the Procrustean bed of the implied loop - hence the need to cancel the default initialization behavior. Your step written using the DoW-loop,

data max (keep=county maxtime maxvalue) ; do until ( last.county ) ; set table1 ; by county ; if valuetime <= maxtime then continue ; maxtime = valuetime ; maxvalue = value ; end; run;

needs no retains of any kind because of the way program control is organized. Note that above, the output after the last record in a by-group has been processed is automatic (there is no OUTPUT statement elsewhere), and before the first record in the group is read, maxtime is initialized to standard missing at the top of the implied loop, which is exactly what is needed for the maximum comparison algorithm - unless by some confluence of circumstances, _. special missing is a legitimate value for VALUETIME. In this case, just insert the instruction

maxtime = ._ ;

before the DoW-loop. Also note that there is only one dot-comparison per by-group (LAST.COUNTY), instead of two (FIRST.COUNTY and LAST.COUNTY).

Kind regards, ================== Paul M. Dorfman Jacksonville, FL ==================

> From: Vanessa Hayden [mailto:vchayden@YAHOO.COM] > Does it have to be a SQL query? This is one of those cases where > using retain in a data step can save you a pass of the data. (The > having clause in SQL produces an extra pass of the data -- and Dave's > excellent solution is the only way I know to solve this problem in > SQL.) If you have a very large data set, or you just feel like > playing with the data step, try the following. > > proc sort data=table1; > by county; > run; > > /* NOTE: keep only the BY and computed RETAIN */ > /* variables. The other variables will contain */ > /* the values that happened to be on the last */ > /* record for the county. A proc would force */ > /* you to omit these variables anyway. */ > data max (keep=county maxtime maxvalue); > set table1; > by county; > retain maxtime maxvalue; > if first.county then > do; > maxtime = valuetime; > maxvalue = value; > end; > else if valuetime > maxtime then > do; > maxtime = valuetime; > maxvalue = value; > end; > if last.county then output; > run;

Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.