subsetting based on two dates

[lillymaginta]

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! 

[PeterClemmensen]

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;

[Ksharp]

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;

[PeterClemmensen]

@lillymaginta Did this solve your problem? 🙂

[lillymaginta]

both produces an error. 

[PeterClemmensen]

What error are you getting?

[PeterClemmensen]

This is my log running my solution

[lillymaginta]

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. 

[PeterClemmensen]

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

[lillymaginta]

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

[PeterClemmensen]

What error are you getting?