BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jdukes42
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
DLing
Obsidian | Level 7

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

3 REPLIES 3
art297
Opal | Level 21

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;

DLing
Obsidian | Level 7

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.

jdukes42
Calcite | Level 5

Thank You for the help.

Jonathan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 2774 views
  • 1 like
  • 3 in conversation