DATA Step, Macro, Functions and more

Collapsing Dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Collapsing Dates

Hi All,

I have a question regarding collapsing dates. Currently, I have in my dataset the variables listed in the table below. The way the datacomes to me is that consecutive dates (i.e. Subject 1) are listed in a separate variable.  I would like to collapse the prescription names and prescription dates that are consecutive into one start/stop date and leave those with non-consecutve dates as  separate whenthey are like Subject 3.   How would one accomplish this? Thanks in advance for the help.

ID

 

rx_name_1

 

rx_name_2

 

rx_start_dt_1

 

rx_start_dt_2

 

rx_stop_dt_1

 

rx_stop_dt_2

 

New_Start1

 

New_Start2

 

New_Stop1

 

New_Stop2

 

1

 

3

 

3

 

2/14/2001

 

2/15/2001

 

2/14/2001

 

2/16/2001

 

2/14/2001

 

 

2/16/2001

 

 

2

 

10

 

3

 

2/12/2001

 

2/13/2001

 

2/13/2001

 

2/13/2001

 

2/12/2001

 

2/13/2001

 

2/13/2001

 

2/13/2001

 

3

 

7

 

7

 

3/20/2001

 

3/25/2001

 

3/20/2001

 

3/27/2001

 

3/20/2001

 

3/25/2001

 

3/20/2001

 

3/27/2001

 

4

 

3

 

3

 

3/20/2001

 

3/25/2001

 

3/21/2001

 

3/22/2001

 

3/20/2001

 

3/25/2001

 

Thanks,

Jonathan


Accepted Solutions
Solution
‎08-05-2011 02:29 PM
Frequent Contributor
Posts: 104

Collapsing Dates

This type of stringing consecutive dates together is easier if the elements are on separate rows rather then columns.

Here's sample code to join records together using data step:

data treatments;

    input id $ rx start:date10. finish:date10. ;

    format start finish yymmdd10.;

cards;

A 3 1jan2001 2jan2001

A 3 2jan2001 6jan2001

A 3 6jan2001 8jan2001

A 3 1feb2001 8feb2001

A 3 10feb2001 20feb2001

A 3 1mar2001 8mar2001

A 3 9mar2001 15mar2001

B 7 15mar2001 18mar2001

B 3 14mar2001 18jun2001

B 7 19mar2001 5apr2001

B 3 19jun2001 9sep2001

B 6 2feb2002 3mar2003

C 4 4apr2004 6apr2004

D 2 2feb2005 3feb2005

E 1 6jun2005 3jun2005

;

proc sort;

    by id rx start;

run;

data longspan(keep=id rxid rxstart rxfinish rename=(rxid=rx rxstart=start rxfinish=finish));

    set treatments;

    by id rx;

    retain rxid rxstart rxfinish;       /*  this is the output record  */

    format rxstart rxfinish yymmdd10.;

    if first.rx then do;                /*  Initialize the record       */

        rxid     = rx;

        rxstart  = start;

        rxfinish = finish;

    end;

    if start <= rxfinish + 1 then rxfinish = finish;    /*  merge record    */

    else do;

        output;                         /*  Write new record    */

        rxid     = rx;

        rxstart  = start;

        rxfinish = finish;              /*  Initialize new record   */

    end;

    if last.rx then output;             /*  Last record always output   */

run;

This code works fine as long as there are no logical inconsistencies such as record E where the finish date is before the start date.
This general structure works when there are multiple event records rather than just two as in your original data.  It also works when they are scattered (sorting brings them back together).

Hope this helps you to move closer to what you're looking to do.

View solution in original post


All Replies
PROC Star
Posts: 7,363

Collapsing Dates

Either your example has some errors in it or I don't quite understand what you are looking to do.  Hopefully, the following gives you an idea of how to solve it:

data want;

  set have;

  format new_rx_start_dt_1  new_rx_start_dt_2

         new_rx_stop_dt_1  new_rx_stop_dt_2 mmddyy10.;

  if rx_start_dt_2 le rx_stop_dt_1+1 then do;

    new_rx_start_dt_1=rx_start_dt_1;

    new_rx_stop_dt_1=rx_stop_dt_2;

  end;

  else do;

    new_rx_start_dt_1=rx_start_dt_1;

    new_rx_stop_dt_1=rx_stop_dt_1;

    new_rx_start_dt_2=rx_start_dt_2;

    new_rx_stop_dt_2=rx_stop_dt_2;

  end;

run;

Solution
‎08-05-2011 02:29 PM
Frequent Contributor
Posts: 104

Collapsing Dates

This type of stringing consecutive dates together is easier if the elements are on separate rows rather then columns.

Here's sample code to join records together using data step:

data treatments;

    input id $ rx start:date10. finish:date10. ;

    format start finish yymmdd10.;

cards;

A 3 1jan2001 2jan2001

A 3 2jan2001 6jan2001

A 3 6jan2001 8jan2001

A 3 1feb2001 8feb2001

A 3 10feb2001 20feb2001

A 3 1mar2001 8mar2001

A 3 9mar2001 15mar2001

B 7 15mar2001 18mar2001

B 3 14mar2001 18jun2001

B 7 19mar2001 5apr2001

B 3 19jun2001 9sep2001

B 6 2feb2002 3mar2003

C 4 4apr2004 6apr2004

D 2 2feb2005 3feb2005

E 1 6jun2005 3jun2005

;

proc sort;

    by id rx start;

run;

data longspan(keep=id rxid rxstart rxfinish rename=(rxid=rx rxstart=start rxfinish=finish));

    set treatments;

    by id rx;

    retain rxid rxstart rxfinish;       /*  this is the output record  */

    format rxstart rxfinish yymmdd10.;

    if first.rx then do;                /*  Initialize the record       */

        rxid     = rx;

        rxstart  = start;

        rxfinish = finish;

    end;

    if start <= rxfinish + 1 then rxfinish = finish;    /*  merge record    */

    else do;

        output;                         /*  Write new record    */

        rxid     = rx;

        rxstart  = start;

        rxfinish = finish;              /*  Initialize new record   */

    end;

    if last.rx then output;             /*  Last record always output   */

run;

This code works fine as long as there are no logical inconsistencies such as record E where the finish date is before the start date.
This general structure works when there are multiple event records rather than just two as in your original data.  It also works when they are scattered (sorting brings them back together).

Hope this helps you to move closer to what you're looking to do.

Occasional Contributor
Posts: 11

Collapsing Dates

Thank You for the help.

Jonathan

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 543 views
  • 1 like
  • 3 in conversation