Survival analysis - creating "failure" variable

[William Mitchell]

Dear all,

I'm hoping for some advice re. creating a 'failure" variable for a survival (or time till event) analysis I'm working on.

We're looking at intraocular pressure (IOP) after surgery.
"Failure" is either (i) IOP >21, (ii) IOP <20% decrease from first IOP, or (iii) re-operation.

I have my data in long format, where IOP's are documented (_iop), time from first op calculated (iop_days), and observations since a unique surgery numbered (iop_no), using

bysort patient_guid _date (iop_date): gen iop_no = _n

I have merged them to unique patient IDs, using the merge m:m function.

I hope the screenshot (attached) is of help.

I would really appreciate any advice anyone has for me, regarding creating these failure variables.
Thanks very much for your time, and consideration.

Will

[Clyde Schechter]

I hope the screenshot (attached) is of help.

Well, it isn't very helpful. In fact, screenshots in general are not helpful and are strongly discouraged here on Statalist. The helpful way to show example data is with the -dataex- command. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

In addition to the form of the example, there are also problems with the content you show. For your example to be useful, you need to include a patient id variable, and some variable or variables that would enable someone to know when a re-operation has occurred.

Please post back with a better example. Thanks.

[William Mitchell]

Thanks very much for clarifying that for me, I apologise for posting unhelpful material.

I have used the dataex command, where:
- id_no is the unique patient ID and the eye (where a different eye will be treated as a different event)
- proc_date is the original date of the procedure
- iop_days is the no. of days since the procedure that the IOP was measured
- iop_no refers to the sequence/order the IOP measurement was taken
- iop refers to the IOP measurement (in mmHg); as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str41 id_no str8 proc_date float(iop_days iop_no) int iop
"000049Right" "12/5/13"   -4  1 22
"000049Right" "12/5/13"  276  2 24
"000049Right" "12/5/13" 1163  3 24
"000049Right" "12/5/13" 1612  4 24
"000049Right" "12/5/13" 1612  5 22
"000049Right" "12/5/13" 1658  6 24
"000049Right" "12/5/13" 1658  7 22
"000049Right" "12/5/13" 1721  8 24
"000049Right" "12/5/13" 1721  9 22
"000049Right" "12/5/13" 1812 10 24
"000049Right" "12/5/13" 1812 11 22
"000049Right" "12/5/13" 1812 12 22
"000049Right" "12/5/13" 2004 13 22
"000049Right" "12/5/13" 2004 14 24
"00005fLeft"  "20/6/08" 3091  1 13
"00005fLeft"  "20/6/08" 3118  2 17
"00005fRight" "31/3/14" -745  1 17
"00005fRight" "31/3/14" -745  2 17
"00005fRight" "31/3/14" -717  3 14
"00005fRight" "31/3/14" -717  4 14
"00005fRight" "31/3/14"  -20  5 18
"00005fRight" "31/3/14"  -20  6 18
"00005fRight" "31/3/14"    1  7 15
"00005fRight" "31/3/14"   22  8 20
"00005fRight" "31/3/14"  949  9 16
"00005fRight" "31/3/14"  953 10 16
"00005fRight" "31/3/14"  953 11 14
"00005fRight" "31/3/14"  953 12 14
"00005fRight" "31/3/14"  981 13 15
"00005fRight" "31/3/14" 1092 14 13
"00005fRight" "31/3/14" 1092 15 13
"00005fRight" "31/3/14" 1274 16 15
"00005fRight" "31/3/14" 1274 17 15
"00005fRight" "31/3/14" 1456 18 13
"00005fRight" "31/3/14" 1456 19 15
"0000aaLeft"  "20/1/12" -139  1 20
"0000aaLeft"  "20/1/12" -139  2 21
"0000aaLeft"  "20/1/12"   -1  3 20
"0000aaLeft"  "20/1/12"   -1  4 20
"0000aaLeft"  "20/1/12"   12  5 20
"0000aaLeft"  "20/1/12"   12  6 20
"0000aaLeft"  "20/1/12"  215  7 19
"0000aaLeft"  "20/1/12"  215  8 20
"0000aaLeft"  "20/1/12"  537  9 19
"0000aaLeft"  "20/1/12"  537 10 18
"0000aaLeft"  "20/1/12"  575 11 19
"0000aaLeft"  "20/1/12"  575 12 19
"0000aaLeft"  "20/1/12"  958 13 20
"0000aaLeft"  "20/1/12"  958 14 20
"0000aaLeft"  "20/1/12"  958 15 20
"0000aaLeft"  "20/1/12" 1266 16 20
"0000aaLeft"  "20/1/12" 1266 17 19
"0000aaLeft"  "20/1/12" 1672 18 20
"0000aaLeft"  "20/1/12" 2057 19 17
"0000aaLeft"  "20/1/12" 2057 20 19
"0000b0Right" "4/1/16"     .  1  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b0Right" "4/1/16"     .  2  .
"0000b8Left"  "2/12/17"    7  1 16
"0000b8Left"  "2/12/17"   30  2 10
"0000b8Right" "9/12/17"  -76  1 14
"0000b8Right" "9/12/17"  -76  2 14
"0000b8Right" "9/12/17"   -6  3 13
"0000b8Right" "9/12/17"   -6  4 13
"0000b8Right" "9/12/17"    1  5 13
"0000b8Right" "9/12/17"   23  6 10
"0000b8Right" "9/12/17"  142  7 12
"0000caLeft"  "9/4/17"   -56  1 15
"0000caLeft"  "9/4/17"     0  2 16
"0000caRight" "26/3/17"  -42  1 17
"0000caRight" "26/3/17"   14  2 16
"00011Left"  "21/1/18" -895  1 16
"00011Left"  "21/1/18" -895  2 15
"00011Left"  "21/1/18" -442  3 16
"00011Left"  "21/1/18" -442  4 15
"00011Left"  "21/1/18"  -69  5 14
"00011Left"  "21/1/18"  -69  6 14
"00011Left"  "21/1/18"  -57  7 18
"00011Left"  "21/1/18"  -57  8 15
"00011Left"  "21/1/18"  -13  9 16
"00011Left"  "21/1/18"    1 10 17
"00011Left"  "21/1/18"    8 11 11
"00011Left"  "21/1/18"   30 12 16
"00011Left"  "21/1/18"   30 13 16
"00011Left"  "21/1/18"  213 14 11
"00011Left"  "21/1/18"  213 15 10
"00011Right" "7/1/18"    15  1 17
end

As above, we're hoping to calculate "failure" variables, where "failure" is either (i) IOP >21, (ii) IOP <20% decrease from first IOP, or (iii) re-operation.
I think the only way to see whether there has been a re-operation is to see if there is a unique ID/eye laterality that is repeated, on a different date.

Thanks so much for your consideration. I hope this is more helpful information.

Will

[Clyde Schechter]

The following code gives you what you asked for:

gen _proc_date = daily(proc_date, "DM20Y")
assert missing(_proc_date) == missing(proc_date)
format _proc_date %td
drop proc_date
rename _proc_date proc_date

drop if missing(id_no, proc_date, iop_days, iop)
gen byte iop_gt_21 = iop > 21 & iop_days > 0
by id_no (iop_days), sort: gen byte decrease_lt_20_pct ///
    = iop > 0.8*iop[1] & iop_days > 0
by id_no (iop_days): gen byte re_op = proc_date > proc_date[1]

by id_no (iop_days): egen failure_day = ///
    min(cond(iop_gt_21 | decrease_lt_20_pct | re_op, iop_days, .))
by id_no (iop_days): egen byte failed = max(!missing(failure_day))

But I doubt it is really what you want. I'm no ophthalmologist, but I know enough about this material to say that your definition of failure does not make sense to me in the context of this data. Here's why:

1. First, I think that the criteria should only apply to observations post-op. You don't impose that restriction. The code above does.
2. Even so, (ii) might make sense if each patient-eye had a single baseline iop measurement shortly before surgery. But in your data, some people have none, and some people have a long string of them. If a person has no pre-op baseline measurement, then on the very first post-op measurement they fail on this criterion because the decrease from the first iop is zero. If a person has a long string of them, it is likely that the very first will be somewhat remote in time before surgery, and over time the trend in iop may well be upward. So you are almost asking the surgery to get them down below 80% of their pre-morbid iop. That's a huge ask. I think it would make more sense to use as the baseline the last pre-op IOP, or perhaps an average of all the pre-op iop's, and if there are no pre-op IOPs this criterion is not applicable. But I did not write the code to reflect this.
3. There are observations in your data set where the iop is missing. Clearly these eyes cannot be evaluated as failures or not. To simplify the code, I dropped them rather than adding extra code to get around this problem.

Note that for this code to work properly, your string variable proc_date has to be converted to a Stata internal format date variable, otherwise it is not possible to correctly sort things in chronological order. This is taken care of at the beginning of the code.

[William Mitchell]

Thanks so much for this, and for your insight re. your points above. I've replied below;

1. The criteria should only apply to post-operative IOPs, thank you

2. Baseline IOP should be pre-op; either (a) the most immediate pre-op IOP, or (b) an average of all pre-op IOPs (as you say).
Unfortunately not everyone has a pre-op IOP, so this won't always be possible - but using the most immediate post-op IOP also won't suffice as a 'baseline' either.
Would it be possible to see the code to reflect this, if the baseline were (a) the most immediate pre-op IOP?

3. Dropping the eyes with missing IOP is great. Thank you.

I note you have converted the proc_date variable to an internal Stata format date variable. Thanks so much.

[Clyde Schechter]

The following code calculates the baseline iop as the average of all pre-op iop's

gen _proc_date = daily(proc_date, "DM20Y")
assert missing(_proc_date) == missing(proc_date)
format _proc_date %td
drop proc_date
rename _proc_date proc_date

//  CALCULATE BASE LINE IOP AS AVERAGE OF ALL PRE-OP IOPS
by id_no, sort: egen baseline_iop = mean(cond(iop_days < 0, iop, .))

drop if missing(id_no, proc_date, iop_days, iop)
gen byte iop_gt_21 = iop > 21 & iop_days > 0
by id_no (iop_days), sort: gen byte decrease_lt_20_pct ///
    = iop > 0.8*baseline_iop & iop_days > 0
by id_no (iop_days): gen byte re_op = proc_date > proc_date[1]

by id_no (iop_days): egen failure_day = ///
    min(cond(iop_gt_21 | decrease_lt_20_pct | re_op, iop_days, .))
by id_no (iop_days): egen byte failed = max(!missing(failure_day))

Changes in bold face.

[William Mitchell]

Dear Stata List

I'm looking for advice regarding:

(i) creating a "with_slt" variable that is 1 if the patient had an SLT procedure within 1-year prior their procedure, and
(ii) making sure I only count each individual procedure once.

My data is below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str37 id_no float(proc_date _sltdate with_slt) byte(re_op failed)
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"000049Right" 19490 . 0 0 1
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left" 19813 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"00005f6Left"  17703 . 0 0 0
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000aaaLeft"  19012 . 0 0 1
"0000b8bLeft"  21155 . 0 0 0
"0000b8bRight" 21162 . 0 0 1
"0000b8bRight" 21162 . 0 0 1
"0000b8bRight" 21162 . 0 0 1
"0000b8bRight" 21162 . 0 0 1
"0000b8bRight" 21162 . 0 0 1
"0000ca7cLeft"  20918 . 0 0 0
"0000ca7cRight" 20904 . 0 0 0
"00011730Left"  21205 . 0 0 1
"00011730Left"  21205 . 0 0 1
"00011730Left"  21205 . 0 0 1
"00011730Left"  21205 . 0 0 1
"00011730Left"  21205 . 0 0 1
"00011730Left"  21205 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"00011cd7Left"  20318 . 0 0 1
"000161efRight" 21252 . 0 0 0
"000161efLeft"  21287 . 0 0 1
"000161efLeft"  21287 . 0 0 1
"000161efLeft"  21287 . 0 0 1
"00016b03Left"  20784 . 0 0 0
"00016b03Right" 20811 . 0 0 0
"0002584eRight" 19842 . 0 0 0
"0002584eRight" 19842 . 0 0 0
"0002584eRight" 19842 . 0 0 0
"0002584eRight" 19842 . 0 0 0
"0002584eRight" 19842 . 0 0 0
end
format %td proc_date
format %td _sltdate

Regarding (i): I have so far only been able to figure out how to create a with_slt variable, without specifying that it has to be <1 year prior the proc_date.
Any advice re. how to do this would be greatly appreciate:

gen with_slt=.
replace with_slt=1 if _slt_eye!=.
replace with_slt=0 if _slt_eye==.
replace with_slt=0 if _sltdate > proc_date

Regarding (ii): some of the unique patient IDs (which include an id_no + the side of the eye operation) recur on a subsequent later date.
These dates are important to define the re_op and failed variables (above); but shouldn't also be included as their own separate observations thereafter...
Any advice regarding how to make sure they're not re-counted themselves would also be greatly appreciated.

Thanks so much for your consideration.

William

PS - I'm re-posting here for continuity (same project, related question). I apologise if this isn't the best place to post.

[Clyde Schechter]

Your example doesn't contain the variable _slt_eye, which, I take it, identifies people who had an SLT procedure. It also has only missing values for _slt_date, which, I assume is the date at which such a procedure would have occurred. You also don't say how to handle the situation where a person has an SLT procedure within a year prior to a second or subsequent operation. I'm guessing in light of (ii) that you probably don't want that to count, but it's not entirely clear.

So please post back with a better data example and a clarification.

[William Mitchell]

Dear Clyde,

I've included the _slt_eye variable in the new dataex paste, along with the other variables from previous post.
It identifies the side (right or left eye) that people who underwent an SLT procedure were operated on.
I encoded the original string variable (slt_eye) into _slt_eye, for the purpose of making the with_slt variable.
Please find included here:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str37 id_no float(proc_date _sltdate) long _slt_eye float with_slt byte(re_op failed)
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"000049Right" 19490 . . 0 0 1
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft" 19813 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"00005fLeft"  17703 . . 0 0 0
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000aaa4Left"  19012 . . 0 0 1
"0000b8b8Left"  21155 . . 0 0 0
"0000b8b8Right" 21162 . . 0 0 1
"0000b8b8Right" 21162 . . 0 0 1
"0000b8b8Right" 21162 . . 0 0 1
"0000b8b8Right" 21162 . . 0 0 1
"0000b8b8Right" 21162 . . 0 0 1
"0000ca7cLeft"  20918 . . 0 0 0
"0000ca7cRight" 20904 . . 0 0 0
"00011730Left"  21205 . . 0 0 1
"00011730Left"  21205 . . 0 0 1
"00011730Left"  21205 . . 0 0 1
"00011730Left"  21205 . . 0 0 1
"00011730Left"  21205 . . 0 0 1
"00011730Left"  21205 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"00011cd7Left"  20318 . . 0 0 1
"000161ef7Right" 21252 . . 0 0 0
"000161ef7Left"  21287 . . 0 0 1
"000161ef7Left"  21287 . . 0 0 1
"000161ef7Left"  21287 . . 0 0 1
"000161ef7Left"  20784 . . 0 0 0
"00016b03aRight" 20811 . . 0 0 0
"0002584e0Right" 19842 . . 0 0 0
"0002584e0Right" 19842 . . 0 0 0
"0002584e0Right" 19842 . . 0 0 0
"0002584e0Right" 19842 . . 0 0 0
"0002584e0Right" 19842 . . 0 0 0
end
format %td proc_date
format %td _sltdate
label values _slt_eye _slt_eye

The variable _slt_date is the date the SLT procedure occurred (if they had one).
Of the 3.1 million observations, only ~10% had an SLT procedure, hence missing values (for both _slt_date and _slt_eye).
Only the with_slt variable denotes these people did not have an SLT (when with_slt==0).

I tried to show you what the dates look like, by copying and pasting observations of people with SLT from Stata into excel, then back into Stata again (so the dataex command would capture dates for this post); but they were all converted to string. I also assume this is a horrible technique/fraught with error. I'm not sure how else to solve this, and show you what the variable dates look like, but hope this might help:

. describe id_no proc_date _sltdate _slt_eye with_slt re_op failed

              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------------
id_no           str37   %37s                  
proc_date       float   %td                   
_sltdate        float   %td                   
_slt_eye        long    %11.0g     _slt_eye   
with_slt        float   %9.0g                 
re_op           byte    %8.0g                 
failed          byte    %8.0g

If someone had an SLT after their first procedure, but prior their second procedure, we don't want to count that at all. Thank you for clarifying that.
We are only interested in their first procedure (and whether there was an SLT within a year prior to that, in which case the with_slt/equivalent variable should ==1).

Thanks so much for clarifying all of this. I really appreciate it, and hope this post is more helpful.

William

[Clyde Schechter]

This will get you part of the way there:

by id_no, sort: egen date_first_proc = min(proc_date)
format date_first_proc %td

by id_no: egen slt_within_1_year = max(inrange(_sltdate, date_first_proc-365, ///
    date_first_proc))

What it doesn't do is match the SLT eye with the eye of the procedure. I couldn't do that because in the example you showed, nobody has an SLT procedure, so I could not see how _slt_eye is coded to represent which eye has the SLT procedure. So this code just identifies who has had an SLT within 1 year before their first procedure--but it might not be on the same eye.

If the lateralization of the SLT procedure is important, post back with an example where some of the people actually have an SLT so I can work with the _slt_eye variable.

My earlier point about the dates, not made very clearly I fear, was that to solve this problem I didn't need to know if your dates are MDY or DMY or whatever. All that mattered is that you have Stata internal format numerical date variables (you do).

[William Mitchell]

Thank you so much.

I've copy and pasted the data from Stata here, with data from _slt_eye, _slt_date and _sltdate shown.
Regarding _slt_eye: Left==2, and Right==3.

with_slt    _slt_eye    _slt_date    _sltdate
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Right    2015-03-23    23mar2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Left    2015-05-12    12may2015
1    Right    2015-03-23    23mar2015
1    Right    2015-03-23    23mar2015

. describe with_slt _slt_eye _slt_date _sltdate

              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------------
with_slt        float   %9.0g                 
_slt_eye        long    %11.0g     _slt_eye   
_slt_date       str10   %10s                  
_sltdate        float   %td

[Clyde Schechter]

Please help me help you. I need a single example data that is like the one in #9 but that also has non-missing values for the variables with_slt, _slt_eye and _sltdate. (I don't need the string variable _slt_date). Please create that and post it back with -dataex-. I need to be able to work with the data in Stata to write the code. What you have shown in #11 won't enable me to do that.

[William Mitchell]

I've modified a sample of the code in stata, with non-missing values for with_slt, _slt_eye and _sltdate

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 id_no float proc_date byte with_slt float _sltdate long _slt_eye byte(re_op failed)
"0589afbaLeft" 20515 1 19924 1 0 1
"0589afbaLeft" 20515 1 19294 2 0 1
"0589afbaLeft" 20515 1 19315 1 0 1
"0589afbaLeft" 20515 1 19315 1 0 1
"0589afbaLeft" 20515 1 18895 2 0 1
"0589afbaLeft" 20515 1 19294 2 0 1
"0589afbaLeft" 20515 1 20492 1 0 1
"0589afbaLeft" 20515 1 20492 1 0 1
"0589afbaLeft" 20515 1 19315 1 0 1
"0589afbaLeft" 20515 1 18902 1 0 1
"0589afbaLeft" 20515 0 20729 2 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
"0589afbaLeft" 20515 0 20729 2 0 1
"0589afbaLeft" 20515 1 19294 2 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
"0589afbaLeft" 20515 1 18902 1 0 1
"0589afbaLeft" 20515 1 19294 2 0 1
"0589afbaLeft" 20515 1 20470 2 0 1
"0589afbaLeft" 20515 1 18895 2 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
"0589afbaLeft" 20515 1 19917 2 0 1
"0589afbaLeft" 20515 1 20836 2 0 1
"0589afbaLeft" 20515 1 20492 1 0 1
"0589afbaLeft" 20515 1 19294 2 0 1
"0589afbaLeft" 20515 1 19315 1 0 1
"0589afbaLeft" 20515 1 19917 2 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
"0589afbaLeft" 20515 1 17832 1 0 1
end
format %td proc_date
format %td _sltdate
label values _slt_eye _slteyenew
label def _slteyenew 1 "Left", modify
label def _slteyenew 2 "Right", modify

. describe id_no proc_date with_slt _sltdate _slt_eye re_op failed

              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------------
id_no           str36   %36s                  
proc_date       float   %td                   
with_slt        byte    %8.0g                 
_sltdate        float   %td                   
_slt_eye        long    %8.0g      _slteyenew
                                              
re_op           byte    %8.0g                 
failed          byte    %8.0g

In post #9 (and the original data) the variable variable with_slt is float %9.0g, and here it is byte %8.0g.
I'm not sure how to change that here - and really hope it doesn't make it impossible for you to work with.
I'm really sorry if it does, and will keep looking into how to change it to byte %8.0g, if that is the case.

Thanks again.

Will

[Clyde Schechter]

This example is good. Thanks for worrying about the float vs byte issue--in this instance it doesn't actually matter, but sometimes it could make a difference.

Anyway, this does what you want:

gen proc_eye = "Left":_slteyenew if strpos(id_no, "Left") 
replace proc_eye = "Right":_slteyenew if strpos(id_no, "Right")
label values proc_eye _slteyenew

by id_no, sort: egen first_proc_date = min(proc_date)
format first_proc_date %td
by id_no: egen byte slt_within_1_year = ///
    max(inrange(_sltdate, first_proc_date-365, first_proc_date) ///
    & _slt_eye == proc_eye)

[William Mitchell]

This has worked perfectly. I really appreciate your help.
Thanks so much for your patience and persistence.
William.