Selecting values from panel data

[Al Bothwell]

I have data on visits to a clinic. In the example below, "value date" is the date of the clinic visit. "Value" is the outcome variable of interest. Rx date is the date prescription was given. I would like to create a new variable using every patient's baseline score and then scores occurring a minimum of 3 months after the rx date (instead of the value date). Any help will be appreciated.

* Example generated by -dataex-. For more info, type help dataex
clear
input int(id rx_date value value_date)
 97 21677   53 21769
 97 21677   19 21886
 97 21677   26 22179
 97 21677   94 22247
 97 21677   19 22271
109 21719  138 21892
264 21437   19 21560
264 21437   19 22126
347 21656  464 22007
347 21656 1160 22231
469 21425  290 21487
469 21425  237 21585
469 21425  551 21887
469 21425   39 21963
469 21425   39 22056
469 21425   39 22182
557 21581   20 21727
557 21581   20 21818
557 21581   20 22314
589 21410  876 21518
589 21410  260 21577
589 21410   22 21693
589 21410   31 21868
589 21410   20 22176
589 21410   20 22287
650 21620  132 21647
650 21620  148 21685
650 21620   19 21887
650 21620   19 22391
end
format %tdnn/dd/CCYY rx_date
format %tdnn/dd/CCYY value_date

[Clyde Schechter]

I would like to create a new variable using every patient's baseline score and then scores occurring a minimum of 3 months after the rx date (instead of the value date).

"Using" how? Do you want to average those scores? Or take the highest, or lowest? Or maybe the median? Or the 83rd percentile? Or the earliest, or the latest? Or maybe something altogether different?

And which score is the patient's "baseline?" Is it the first one available in the data set? Or is it identifiable in some other way?

[Al Bothwell]

Thanks for the question, Clyde. The patients' baseline score is the first one available in the dataset. After pulling the baseline score, I want to pull the next visit that occurs at least 3 months after the RX date. Each patient will have two scores (baseline, and at least 3 months after RX date). These two scores are the most clinically meaningful. There will be no averaging.

[Clyde Schechter]

//  FIRST GET BASELINE VALUE
by id (value_date), sort: gen baseline_value = value[1]
by id (value_date), sort: gen baseline_date = value_date[1]

//  VERIFY RX_DATE IS CONSISTENT WITHIN ID
by id (rx_date), sort: assert rx_date[1] == rx_date[_N]

//  FIND THE FIRST VALUE 3 MONTHS (= 91 DAYS) AFTER RX_DATE
gen rx_date_plus_91 = rx_date + 91
rangestat (first) later_value = value, by(id) interval(value_date, ///
    rx_date_plus_91, .)
rangestat (min) later_date = value_date, by(id) interval(value_date, ///
    rx_date_plus_91, .)
drop rx_date_plus_91

//  OPTIONAL:
drop value value_date
format *_date %td
by id, sort: keep if _n == 1

Note: Code assumes 3 months means 91 days. The code can be modified to rely on calendar months instead, but that would be unusual for a scientific study because those vary in length and due to truncation to integers when the dates in question are not on the same day of the month.

The code following the OPTIONAL comment reduces the data set to one observation per id, containing just the rx date, baseline value, baseline date, the selected later value, and its date. If you don't want to take that step, stop before that.

[Clyde Schechter]

Just realized that I forgot to mention that -rangestat- is by Robert Picard, Nick Cox and Roberto Ferrer, and is available from SSC.

[Al Bothwell]

Yes, I installed rangestat from SSC. The code worked for everyone except one patient. This is minor and I will fix it. Thank you for helping.