Retain rows problem

Accepted Solution Solved
Reply
Super Contributor
Posts: 543
Accepted Solution

Retain rows problem

Hi,

I have a file with an event date (Date) and pre-event dates that I rearranged as interval (Start, Stop).

I need to create a First (ever) date and a Recent date, within each event.

Say, for example:

                     id     date    start     stop

                       12006-06-062004-06-012005-04-01
                       12006-06-062005-06-012007-04-01
                       12006-06-062007-06-012008-04-01
                       12006-06-062008-06-012009-04-01
                       12009-01-062004-06-012005-04-01
                       12009-01-062005-06-012007-04-01
                       12009-01-062007-06-012008-04-01
                       12009-01-062008-06-012009-04-01

I have two event dates (2006/06/06 and 2009/01/06) and 4 between events dates. Some before and some other.

I want a file like this:

    id      date     start      stop     first    recent

          12006-06-062004-06-012005-04-012004-06-012005-04-01
          12009-01-062008-06-012009-04-01           2008-06-01

The second event doesn't get a FIRST because there is only one FIRST (ever) per ID, but the most recent for 2009 DATE is the 2008.

This is what I wrote...and it doesn't work (as my brain)

data have;

informat start stop date yymmdd10.;

format start stop date yymmdd10.;

input id start stop date;

datalines;

1 2004/06/01 2005/04/01    2006/06/06

1 2005/06/01 2007/04/01    2006/06/06

1 2007/06/01 2008/04/01    2006/06/06

1 2008/06/01 2009/04/01    2006/06/06

1 2004/06/01 2005/04/01    2009/01/06

1 2005/06/01 2007/04/01    2009/01/06

1 2007/06/01 2008/04/01    2009/01/06

1 2008/06/01 2009/04/01    2009/01/06

;

proc print noobs;var id date start stop;

run;

data want;

    set have;

by id date;

    if start < date then first = start;

    if stop < date then recent = stop;

        format first recent yymmdd10.;

        if recent = . and first ne . then recent = first;

if first.date;* it works great for 2 pre-events date within an event;

*don't know how to tell SAS anything right now;

run;

proc print data = want noobs;

    var id date start stop first recent;

run;

Thank you!

Anca.


Accepted Solutions
Solution
‎04-29-2014 08:44 AM
Super User
Super User
Posts: 7,988

Re: Retain rows problem

Posted in reply to AncaTilea

Try something along the lines of:

proc sql;

  create table WANT as

  select  A.*,

          B.FIRST

          (select MAX(THIS.STOP) from HAVE THIS having THIS.ID=A.ID and THIS.STOP <= A.DATE group by ID) as RECENT

  from    HAVE A

  left join (select MIN(FIRST) as FIRST from HAVE group by ID) B

  on      A.ID=B.ID;

quit;

View solution in original post


All Replies
Regular Contributor
Posts: 151

Re: Retain rows problem

Posted in reply to AncaTilea

In your output example, why does the recent date in the first row take the stop date, but in the second row the start date?

Super Contributor
Posts: 543

Re: Retain rows problem

because the 2009/04/01 is past the 2009/01/06.

And the 2008 is the most recent.

...confusing,yes?

Solution
‎04-29-2014 08:44 AM
Super User
Super User
Posts: 7,988

Re: Retain rows problem

Posted in reply to AncaTilea

Try something along the lines of:

proc sql;

  create table WANT as

  select  A.*,

          B.FIRST

          (select MAX(THIS.STOP) from HAVE THIS having THIS.ID=A.ID and THIS.STOP <= A.DATE group by ID) as RECENT

  from    HAVE A

  left join (select MIN(FIRST) as FIRST from HAVE group by ID) B

  on      A.ID=B.ID;

quit;

Super Contributor
Posts: 543

Re: Retain rows problem

With a couple of edits I got what I wanted!!!

Thank you so much!!!

Anca

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 200 views
  • 0 likes
  • 3 in conversation