Date:         Thu, 14 Dec 2006 10:16:28 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: data step

On Wed, 13 Dec 2006 15:01:38 -0500, Florio Arguillas <foa2@CORNELL.EDU> wrote:

>Hi Souga,
>
>Here's an sql approach.
>
>proc sql;
>create table need as
>select a.*
>from have a,
>(select score from have b group by id having min(t) = t)
>where a.score ne b.score and a.id = b.id
>group by id
>having min(t) = t;
>quit;

This works, somewhat to my surprise. It seems to me that the alias B and the
column B.ID should *not* be surfaced by the inline view to the join. I
thought it would be necessary to use

   (select ID, score from have group by id having min(t) = t) AS B


>
>Florio
>
>
>
>

My DATA step solution:

   data wouldlike(keep = id t);
   do until (last.id);
      set have;
      by id;
      change = sum( change, dif(score) and not first.id );
      if change=1 then output;
      end;
   run;

>At 01:53 PM 12/13/2006, souga soga wrote:
>>Hi,
>>
>>I have a dataset.
>>
>>ID="X";t="9:30:30"; score=10;output;
>>ID="X";t="9:30:32"; score=10;output;
>>ID="X";t="9:30:38"; score=20;output;
>>ID="X";t="9:30:39"; score=40;output;
>>
>>ID="Y";t="9:40:30"; score=10;output;
>>ID="Y";t="9:40:36"; score=10;output;
>>ID="Y";t="9:40:39"; score=90;output;
>>ID="Y";t="9:40:49"; score=70;output;
>>run;
>>
>>For each ID I would like the time at which the score changes the first time.
>>
>>Expected output:
>>
>>ID="X";t="9:30:38";
>>ID="Y";t="9:40:39";
>>
>>
>>Thanks as always,
>>Sa