topic Collapsing Dates in SAS Programming

Collapsing Dates

jdukes42 — Fri, 05 Aug 2011 15:25:21 GMT

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

Collapsing Dates

art297 — Fri, 05 Aug 2011 16:27:02 GMT

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;

Collapsing Dates

DLing — Fri, 05 Aug 2011 18:29:21 GMT

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.

Collapsing Dates

jdukes42 — Fri, 05 Aug 2011 18:33:16 GMT

Thank You for the help.

Jonathan