topic Re: subsetting based on two dates in SAS Programming

subsetting based on two dates

lillymaginta — Sun, 06 Nov 2016 00:11:13 GMT

id start date  drug end_Date 
1 01/01/2005 a     04/01/2005
1  02/01/2005 b    03/04/2005
2  02/03/2005 a    03/04/2005
2   01/02/2004  b    02/02/2004

I have the following database with multiple ids and only two drugs a and b. I want to create a database for drug a but to change the end date based on the start of drug. Here are my assumptions:

For patients 1, the start date is the same but the end date for drug a would be the start of drug b.

For patient 2, do not include because drug b started before a

each drug has one start and one end date

My output would look like this

id start date  drug end_Date 
1 01/01/2005 a     02/01/2005

Thanks!

Re: subsetting based on two dates

PeterClemmensen — Sun, 06 Nov 2016 10:33:11 GMT

Something like this?

data have;
   format id start_date drug end_Date;
   informat start_date end_date ddmmyy10.;
   input
   id $ start_date drug $ end_Date;

   format start_date end_date ddmmyy10.;

   datalines;
   1 01/01/2005 a 04/01/2005
   1 02/01/2005 b 03/04/2005
   2 02/03/2005 a 03/04/2005
   2 01/02/2004 b 02/02/2004
;

proc sort data = have;
   by descending id descending drug;
run;

data want;
   set have;

   if start_date < lag(start_date) then do;
      end_date = lag(start_date);
      if drug='a' then output;
   end;
run;

Re: subsetting based on two dates

Ksharp — Sun, 06 Nov 2016 11:07:46 GMT

Assuming there are only two obs for each id.

data have;
   format id start_date drug end_Date;
   informat start_date end_date ddmmyy10.;
   input
   id $ start_date drug $ end_Date;

   format start_date end_date ddmmyy10.;

   datalines;
   1 01/01/2005 a 04/01/2005
   1 02/01/2005 b 03/04/2005
   2 02/03/2005 a 03/04/2005
   2 01/02/2004 b 02/02/2004
;
run;
data want;
 merge have have(keep=id start_date rename=(id=_id start_date=_date) firstobs=2);
 if id=_id and start_date lt _date then do;
  end_date=_date;output;
 end;
 drop _:;
run;

Re: subsetting based on two dates

PeterClemmensen — Mon, 07 Nov 2016 07:38:06 GMT

@lillymaginta Did this solve your problem? 🙂

Re: subsetting based on two dates

lillymaginta — Mon, 07 Nov 2016 20:43:04 GMT

both produces an error.

Re: subsetting based on two dates

PeterClemmensen — Tue, 08 Nov 2016 07:07:29 GMT

What error are you getting?

Re: subsetting based on two dates

PeterClemmensen — Tue, 08 Nov 2016 07:09:27 GMT

This is my log running my solution

Re: subsetting based on two dates

lillymaginta — Tue, 08 Nov 2016 10:49:29 GMT

in the original dataset that is exactly similar to the one above but with multiple ids, the code create missing data for some of the dates.

Re: subsetting based on two dates

PeterClemmensen — Tue, 08 Nov 2016 14:03:59 GMT

Do you have any IDs for which there are more than two records?

Re: subsetting based on two dates

lillymaginta — Thu, 10 Nov 2016 14:45:01 GMT

no each id would have one period for drug a and another period for drug b. So only two per each.

Re: subsetting based on two dates

PeterClemmensen — Thu, 10 Nov 2016 14:46:29 GMT

What error are you getting?