selecting a specific sequence of records

[Linlin]

Hi Ksharp,

I have learned a lot from you.  Thank you!

I modified your code slightly.

data x;

input id dt : mmddyy. x high low;

format id z7. dt mmddyy10.;

datalines;

0000001    01/01/2010              50       .             1

0000001    02/01/2010              672     1            .

0000001    03/01/2010              50       .             1

0000002    01/01/2011              50       .             1

0000002    02/01/2011              100     1            .

0000002    03/01/2011              50       .             1

0000002    04/01/2011              50       .             1

0000002    05/01/2011              50       .             1

;

run;

data want(drop=count i);

set x;

count=_n_;

if low=1 and (id=lag(id) and lag(high)=1) and (id=lag2(id) and lag2(low)=1)

  then do;

      do i=count-2 to count;

          set x point=i;

          output;

      end;

   end;

run;

[Ksharp]

You are welcome.

By the way. Where are you come from?

[Linlin]

I found out the Forums about two weeks ago.

I am currently working at a University in Ma. Please let me know if you need a SAS programmer.

[lnb001]

Now that I have figured how to keep these three records, I am told I need to transpose the data. I start with this:

Case ID     Date                       X        High        Low

0000001    01/01/2010              50       .             1

0000001    02/01/2010              672     1            .

0000001    03/01/2010              50       .             1

0000002    01/01/2011              50       .             1

0000002    02/01/2011              100     1            .

0000002    03/01/2011              50       .             1

But am told I need to end up with this:

Case ID    Date 1               Date 2               Date 3

000001     01/01/2010        02/01/2010         03/01/2010

000002     01/01/2011        02/01/2011         03/01/2011

Obviously proc transpose would do this easily if I only had one 'trio' for each Case ID. In some cases, however, there are multiple 'trios':

Case ID     Date                       X        High        Low

0000003    01/01/2010              50       .             1

0000003    02/01/2010              672     1            .

0000003    03/01/2010              50       .             1

0000003    04/01/2010              50       .             1

0000003    05/01/2010              999     1            .

0000003    06/01/2010              50       .             1

0000004    01/01/2011              50       .             1

0000004    02/01/2011              100     1            .

0000004    03/01/2011              50       .             1

0000004    04/01/2011              50       .             1

0000004    05/01/2011              300     1            .

0000004    06/01/2011              50       .             1

In this instance, I would want my new table to look like:

Case ID    Date 1               Date 2               Date 3

000003     01/01/2010        02/01/2010         03/01/2010

000003     04/01/2010        05/01/2010         06/01/2010

000004     01/01/2011        02/01/2011         03/01/2011

000004     04/01/2011        05/01/2011         06/01/2011

How can I do this?

[Ksharp]

It is easy.IF you make sure there are three observations in each group.

data x;
input id dt : mmddyy. x high low;
format id z7. dt mmddyy10.;
datalines;
0000003    01/01/2010              50       .             1
0000003    02/01/2010              672     1            .
0000003    03/01/2010              50       .             1
0000003    04/01/2010              50       .             1
0000003    05/01/2010              999     1            .
0000003    06/01/2010              50       .             1
0000004    01/01/2011              50       .             1
0000004    02/01/2011              100     1            .
0000004    03/01/2011              50       .             1
0000004    04/01/2011              50       .             1
0000004    05/01/2011              300     1            .
0000004    06/01/2011              50       .             1
;
run;
data x;
 set x;
 if mod(_n_,3)=1 then do;count=0;group+1;end;
 count+1;
run;
proc transpose data=x out=want(drop=group _name_) prefix=date;
 by id group;
 var dt;
 id count;
run;

Ksharp

[lnb001]

Thanks. This seems to be a step in the right direction, BUT I am left with this when I try to transpose the data:

Case ID     Date                       Date1             Date2             Date3

0000003    01/01/2010              01/01/2010     .                     .

0000003    02/01/2010              .                    02/01/2010      .

0000003    03/01/2010              .                    .                     03/01/2010

0000003    04/01/2010              04/01/2010     .                     .

0000003    05/01/2010              .                    05/01/2010      .

0000003    06/01/2010              .                    .                     06/01/2010

0000004    01/01/2011              01/01/2011     .                     .

0000004    02/01/2011              .                    02/01/2011      .

0000004    03/01/2011              .                    .                     03/01/2011

0000004    04/01/2011              04/01/2011     .                     .

0000004    05/01/2011              .                    05/01/2011      .

0000004    06/01/2011              .                    .                     06/01/2011

[art297]

That definitely ISN'T the result I get when I run KSharp's code.  You must have changed something.

[lnb001]

You're right, I referenced the wrong variable in my 'by' statement. I think I have what I want now. Thank you all.