Time difference between each subset of observations

[Alireza_Boloori]

Hello everyone,

I have a data like this:

x    date

1    01/01/2008

1    01/10/2008

1    02/15/2008

1    03/25/2008

2    01/17/2008

2    04/28/2008

2    05/07/2008

and I want to (1) obtain the difference between dates (in # days) for each specific value of x, and (2) identify observations whose time difference with the previous one is less than 30 days. In other words, I want this data:

x    date                 difference         identifier

1    01/01/2008      NA                   0

1    01/10/2008      9                      1

1    02/15/2008      5                      1

1    03/25/2008      40                    0

2    01/17/2008      NA                   0

2    04/28/2008      63                    0

2    05/07/2008      10                    1

Any idea/help is really appreciated!

[ChrisBrooks · Accepted solution]

This is easy enough except that unless I'm misunderstanding what you want some of the differences in your output aren't correct.

Here is my code

data have;
	infile datalines dlm=",";
	input x date mmddyy10.;
	format date mmddyy10.;
	datalines;
1,01/01/2008
1,01/10/2008
1,02/15/2008
1,03/25/2008
2,01/17/2008
2,04/28/2008
2,05/07/2008
;
run;


proc sort data=have;
	by x;
run;

data want(drop=ldate);
	set have;
	by x;
	ldate=lag(date);
	if first.x then do;
		difference=.;
		identifier=0;
	end;
	else do;
		difference=date-ldate;
		if difference<30 then identifier=1;
		else identifier=0;
	end;
run;

Also SAS uses the missing symbol . instead of NA so my output looks like this

[Shmuel]

data have;
       input x date mmddtt10.;
       format date mmddyy10.;
cards;
1    01/01/2008
1    01/10/2008
1    02/15/2008
1    03/25/2008
2    01/17/2008
2    04/28/2008
2    05/07/2008
; run;

data want;
 set have;
  by x;
       prev_dt = lag(date);
       if first.x then prev_dt = .;
       difference = date - prev_dt;
       if difference < 30 then identifier = 1;
run;