DATA Step, Macro, Functions and more

subsetting based on two dates

Reply
Frequent Contributor
Posts: 128

subsetting based on two dates

[ Edited ]
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! 

PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

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;
Super User
Posts: 10,028

Re: subsetting based on two dates

Posted in reply to lillymaginta

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;
PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

@lillymaginta Did this solve your problem? Smiley Happy

Frequent Contributor
Posts: 128

Re: subsetting based on two dates

both produces an error. 

PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

What error are you getting?

PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

This is my log running my solution

 

LOGLOG.PNG

Frequent Contributor
Posts: 128

Re: subsetting based on two dates

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. 

PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

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

Frequent Contributor
Posts: 128

Re: subsetting based on two dates

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

PROC Star
Posts: 751

Re: subsetting based on two dates

Posted in reply to lillymaginta

What error are you getting?

Ask a Question
Discussion stats
  • 10 replies
  • 325 views
  • 0 likes
  • 3 in conversation