BookmarkSubscribeRSS Feed
Linlin
Lapis Lazuli | Level 10

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
Super User

You are welcome.

By the way. Where are you come from?

Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Opal | Level 21

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

lnb001
Calcite | Level 5

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 22 replies
  • 1822 views
  • 0 likes
  • 6 in conversation