Date: Fri, 6 Feb 2004 03:17:49 -0800
Reply-To: kanthan <airaha_m@REDIFFMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: kanthan <airaha_m@REDIFFMAIL.COM>
Organization: http://groups.google.com
Subject: Re: If last question--data question
Content-Type: text/plain; charset=ISO-8859-1
Hi,
I have another answer for this problem. I use a compound index and
last. variables. See the code below:
data herd (index=(comp_inx=(herdcode testdate)));
input herdcode string testdate : mmddyy8. ;
format testdate mmddyy8.;
cards ;
427500064 11 09/20/03
427500065 99 09/20/03
427500065 01 11/14/03
427500065 02 11/14/03
427500065 90 11/14/03
run ;
data bound_values (drop=string);
set herd;
by herdcode testdate;
if last.testdate & last.herdcode then output;
run;
data final;
set bound_values;
do while(_IORC_=%sysrc(_sok));
set herd key=comp_inx;
if ¬_error_ then output;
end;
run;
O/P of datset-final is:
Obs herdcode testdate string
1 427500064 09/20/03 11
2 427500065 11/14/03 1
3 427500065 11/14/03 2
4 427500065 11/14/03 90
-I am sure that u would see some error messages in the log just
because of the search againt datset-herd with the index. Might be
somceone can fine tune my code so that the error messages can be
suppressed.
-Kanthan !
-----------------------------------------------------------------------------
Howard_Schreier@ITA.DOC.GOV (Howard Schreier) wrote in message news:<s020d5e7.032@hchb.ita.doc.gov>...
> 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.
