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 (February 2004, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Wed, 4 Feb 2004 11:22:01 -0500
Reply-To:   Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:   Re: If last question--data question
Content-Type:   text/plain; charset=US-ASCII

The SQL solution is probably the most straightforward. You will have to place the GROUP BY clause before the HAVING clause.

DATA step solutions also exist.

The classic approach would be a two-stepper. First list the dates of interest:

data lastdates; set herd; by herdcode ; keep herdcode testdate; if last.herdcode; run;

Then merge with the original data and filter:

data getlast2; merge herd lastdates(in=lastdate); by herdcode testdate; if lastdate; run;

An alternative is to interleave the dataset with itself (two passes in one step). In this case an explicit loop is suitable:

data getlast3(drop = lastdate); do until (last.herdcode); set herd(in=preview) herd ; by herdcode; if preview then lastdate = testdate; else if lastdate = testdate then output; end; run;

The first pass detects the date of interest and the second pass does the filtering.

On Wed, 4 Feb 2004 10:40:26 -0500, Chakravarthy, Venky <Venky.Chakravarthy@PFIZER.COM> wrote:

>I did not group by herdcode in my proposed solution. That should read as: > >proc sql ; > create table getlast as > select * > from herd > having testdate = max(testdate) > group by herdcode ; >quit ; > >_________________________________ >Venky Chakravarthy >E-mail: swovcc_AT_hotmail_DOT_com > >-----Original Message----- >From: Chakravarthy, Venky [mailto:Venky.Chakravarthy@PFIZER.COM] >Sent: Wednesday, February 04, 2004 10:35 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: If last question--data question > > >Thanks for the clarification. Looks like you want the last testdate. In that >case SQL would be a good tool: > >data herd ; > input herdcode string testdate : mmddyy8. ; > format testdate mmddyys8. ; > cards ; >427500065 99 09/20/03 >427500065 01 11/14/03 >427500065 02 11/14/03 >427500065 90 11/14/03 >run ; > >proc sql ; > create table getlast as > select * > from herd > having testdate = max(testdate) ; >quit ; > >_________________________________ >Venky Chakravarthy >E-mail: swovcc_AT_hotmail_DOT_com > >-----Original Message----- >From: Crystal Vierhout [mailto:vierhout@UNITY.NCSU.EDU] >Sent: Wednesday, February 04, 2004 10:24 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: If last question--data question > > >Sorry, I didn't make it as clear as I should of... > >The last testdate needs to stay. In most cases in the dataset I don't have >a problem but only a few herdcode become a problem. > >(1) this logic will fail when you have only one observation within a >herdcode group. > >A herd(herdcode) may change from a 99 to 90 and that is what is getting into >trouble. In most cases a herd will > >herdcode string testdate >427500065 01 11/14/03 >427500065 02 11/14/03 >427500065 90 11/14/03 > >or > >herdcode string testdate >427500065 99 11/14/03 > >When a herd has changed from a 90(that also has a undetermined number of >strings after it) to a 99(with no string) is when problems occur. > >(2) Would it always be the first observation within a herdcode group that is >eliminated? No, > >(3) Or is the row with string flagged "99" marked for deletion and can >appear as any observation within the group? No, there will be a 90 or 99.. > >(4) Or is it the date following similar logic? > Date is the key > > >Hope I stated it better this time...thanks for helping. > >Crystal, > > >LEGAL NOTICE >Unless expressly stated otherwise, this message is confidential and may be >privileged. It is intended for the addressee(s) only. Access to this E-mail >by anyone else is unauthorized. If you are not an addressee, any disclosure >or copying of the contents of this E-mail or any action taken (or not taken) >in reliance on it is unauthorized and may be unlawful. If you are not an >addressee, please inform the sender immediately.


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