calculate days from lagging dates

[bijayadhikar]

Hi, I would like to calculate days difference from lagging dates if subject_ID appears more than 1 times. e.g. ID 1 appears twice, so I would like to calculate number of days from 2nd infection date (i.e. 15-Apr-15) to 1st treatment date (i.e. 31-Mar-15) =15. If there is only one ID, days=.

subject	sub_id	infection_date	treatmet_date	days
1	11	1jan2015	31mar2015	.
1	12	15apr2015	30jun2015	15
2	22	1Sep2015	28feb2015	63
3	33	1jan2015	28feb2015	.
3	34	15mar2015	31mar2015	15
3	35	1may2015	30jun2015	31
4	44	1mar2015	31mar2015	.
5	55	1mar2015	31mar2015
5	56	1oct2015	30jun2015	184

SAS dataset:

data have;
input subject sub_id (infection_date treatmet_date) (: $20.);
cards;
1 11 1jan2015 31mar2015
1 12 15apr2015 30jun2015
2 22 1Sep2015 28feb2015
3 33 1jan2015 28feb2015
3 34 15mar2015 31mar2015
3 35 1may2015 30jun2015
4 44 1mar2015 31mar2015
5 55 1mar2015 31mar2015
5 56 1oct2015 30jun2015
;
run;

Thanks in advance

[mkeintz · Accepted solution]

Thank you for providing a DATA HAVE step, but your code reads in the date variables as character values.  It needs to be read in as a DATE variable,  i.e. a numeric value that represents calendar dates.  So you need to use a proper INFORMAT to read infection_date and treatmet_date.

Once you have done that, you should be able to use code like to below to selectively generate days between current infection date and lagged treatment date:

data want (drop=_:);
  set have;
  by subject;
  _lagtd=lag(treatmet_date);
  if first.subject=0 then days=infection_date - _lagtd;
run;

This code assumes your data are sorted by subject, and chronologically within subject.