create time stop

[tomas-andriotti]

Hi,

I have a longitudinal dataset with many ED (emergency department) visits per individual (id). Each visit has an index date (begdate) and the variable year. I am trying to model a cox for recurrent events creating time0 (start) and time2 (stop).

The main variables are:

id: individuals

k: observations or visits

begdate : date of the observations - visit the ED

year : calendar year 

freq_user_y : 4 visits or more to the ED a year

I created the difference between the observations and the lag time but it turns out the observations k=1 are being excluded because they do not have a time2. And the lag time has been created just for the observations k >= 2 .

I wonder how I can create a time 2 for the first observation then the difference between time 1 and 2 (t2-t1) be kept in this line (k=1), not in the second visit (k=2) and so on, time difference t3-t2 in line k=2, not in k=3...

Look forward to hearing from you.

Tomas

[noling]

I think this is what you're asking for, but I'm not sure it's what you really want:

data have;
	length id k date 8.; 
	format date mmddyy10.;
	do id = 1 to 2;
		do k = 1 to 4;
			date = '06MAR2019'd + (k*k);
			output;
		end;
	end;
run;
data temp;
	set have;
	by id notsorted;
	format prev_date mmddyy10.;
	prev_date= lag(date);

	if first.id then call missing(prev_date);
	diff=date - prev_date;
run;

*now scoot all the diff's up a line. 
What do you want to do with the final diff per id?;
data want(rename=(temp_id = id temp_k=k temp_date=date));
	set temp;
	length temp_id temp_k temp_date 8.;
	format temp_date mmddyy10.;
	retain temp_id temp_k temp_date;

	if not missing(diff) then output;

	temp_id = id;
	temp_k= k;
	temp_date = date;
	keep diff temp_id temp_k temp_date;
run;

Creates:

So here, diff is the difference between (k+1) - k. Is this what you need? I would think through your problem before copying this logic. 

[tomas-andriotti]

Hi,

I ran the command and it created another table with the difference in line 1 for all individuals even the ones with no second observation because there are individuals with no k2. So, I do have a dataset have and what I need is to scoot up the diff only for those individuals with a recurrent event ( k>2) those with no second event should not have a difference because they were not observed or followed up to a second time.

Look forward to hearing from you.

Tomas

[noling · Accepted solution]

Ok, so only scooting up records that have more than 1 ID records. I changed my sample data and added a "BY" statement and second output condition for single ID records:

data have;
	length id k date 8.; 
	format date mmddyy10.;
	id=1;
		do k = 1 to 4;
			date = '06MAR2019'd + (k*k);
			output;
		end;
	k=1;date = '06MAR2019'd;
	id=2; output;
	id=3; output;

run;
data temp;
	set have;
	by id notsorted;
	format prev_date mmddyy10.;
	prev_date= lag(date);

	if first.id then call missing(prev_date);
	diff=date - prev_date;
run;

*now scoot ONLY the diff's up a line if their id is in more than one row.
Output the single ID records; 
data want(rename=(temp_id = id temp_k=k temp_date=date));
	set temp;
	by id;
	length temp_id temp_k temp_date 8.;
	format temp_date mmddyy10.;
	retain temp_id temp_k temp_date;

	if not missing(diff) then output;

	temp_id = id;
	temp_k= k;
	temp_date = date;
	keep diff temp_id temp_k temp_date;
	
	*output again for single records with only 1 ID row;
	if first.id and last.id then output;
run;

Yields:

[tomas-andriotti]

Hey thanks !
I believe it worked. I just have to merge table want with the dataset I have.
Best,
Tomas