creating an indix visit

[Jake Magel]

Hello, I am trying to create an "index visit" where an individual has 90 days of eligibly period before the index visit and 365 days after the index visit.

Eligibily date is "pert_Eligible_Period_Dt and the index visit date is "pert_service_start_dt

All of my cases have at least 15 months of continuous eligibility.


I realize this is a simple coding problem but I have not been successful in working through this! If you can point me in the right direction that would be great.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long unique_member_id str10 pert_Eligible_Period_Dt float(eligibility_date2 pert_service_start_dt_rc)
15 "2013-01-01" 39373 39699
15 "2013-02-01" 39404 39699
15 "2013-03-01" 39432 39699
15 "2013-04-01" 39463 39699
15 "2013-05-01" 39493 39699
15 "2013-06-01" 39524 39699
15 "2013-07-01" 39554 39699
15 "2013-08-01" 39585 39699
15 "2013-09-01" 39616 39699
15 "2013-10-01" 39646 39699
15 "2013-11-01" 39677 39699
15 "2013-12-01" 39707 39699
15 "2014-01-01" 39738 39699
15 "2014-02-01" 39769 39699
15 "2014-03-01" 39797 39699
15 "2014-04-01" 39828 39699
15 "2014-05-01" 39858 39699
15 "2014-06-01" 39889 39699
15 "2014-07-01" 39919 39699
15 "2014-08-01" 39950 39699
15 "2014-09-01" 39981 39699
15 "2014-10-01" 40011 39699
15 "2014-11-01" 40042 39699
20 "2015-02-27" 40160 40446
20 "2015-03-30" 40191 40446
20 "2015-04-29" 40221 40446
20 "2015-05-30" 40252 40446
20 "2015-06-29" 40282 40446
20 "2015-07-30" 40313 40446
20 "2015-08-30" 40344 40446
20 "2015-09-29" 40374 40446
20 "2015-10-30" 40405 40446
20 "2015-11-29" 40435 40446
20 "2015-12-30" 40466 40446
20 "2016-01-30" 40497 40446
20 "2016-02-28" 40526 40446
20 "2016-03-30" 40557 40446
20 "2016-04-29" 40587 40446
21 "2012-12-30" 39371 39877
21 "2013-01-30" 39402 39877
21 "2013-02-27" 39430 39877
21 "2013-03-30" 39461 39877
21 "2013-04-29" 39491 39877
21 "2013-05-30" 39522 39877
21 "2013-06-29" 39552 39877
21 "2013-07-30" 39583 39877
21 "2013-08-30" 39614 39877
21 "2013-09-29" 39644 39877
21 "2013-10-30" 39675 39877
21 "2013-11-29" 39705 39877
21 "2013-12-30" 39736 39877
21 "2014-01-30" 39767 39877
21 "2014-02-27" 39795 39877
21 "2014-03-30" 39826 39877
21 "2014-04-29" 39856 39877
21 "2014-05-30" 39887 39877
21 "2014-06-29" 39917 39877
21 "2014-07-30" 39948 39877
21 "2014-08-30" 39979 39877
21 "2014-09-29" 40009 39877
21 "2014-10-30" 40040 39877
21 "2014-11-29" 40070 39877
21 "2014-12-30" 40101 39877
21 "2015-01-30" 40132 39877
21 "2015-02-27" 40160 39877
21 "2015-03-30" 40191 39877
21 "2015-04-29" 40221 39877
21 "2015-05-30" 40252 39877
21 "2015-06-29" 40282 39877
21 "2015-07-30" 40313 39877
21 "2015-08-30" 40344 39877
21 "2015-09-29" 40374 39877
21 "2015-10-30" 40405 39877
21 "2015-11-29" 40435 39877
21 "2015-12-30" 40466 39877
23 "2012-12-29" 39370 39837
23 "2013-01-29" 39401 39837
23 "2013-02-26" 39429 39837
23 "2013-03-29" 39460 39837
23 "2013-04-28" 39490 39837
23 "2013-05-29" 39521 39837
23 "2013-06-28" 39551 39837
23 "2013-07-29" 39582 39837
23 "2013-08-29" 39613 39837
23 "2013-09-28" 39643 39837
23 "2013-10-29" 39674 39837
23 "2013-11-28" 39704 39837
23 "2013-12-29" 39735 39837
23 "2014-01-29" 39766 39837
23 "2014-02-26" 39794 39837
23 "2014-03-29" 39825 39837
23 "2014-04-28" 39855 39837
23 "2014-05-29" 39886 39837
23 "2014-06-28" 39916 39837
23 "2014-07-29" 39947 39837
23 "2014-08-29" 39978 39837
23 "2014-09-28" 40008 39837
23 "2014-10-29" 40039 39837
23 "2014-11-28" 40069 39837
23 "2014-12-29" 40100 39837
end
format %td eligibility_date2
format %td pert_service_start_dt_rc
[/CODE]

[Clyde Schechter]

I understand neither your data nor what you want to do.

The eligibilty_dat2 and pert_service_start_dt_rc variables in your data are all in the distant future, whereas your pert_Eligibility_Period_Dt (which is not a date variable at all, but a string) represents dates in the recent past. What's going on there?

And what is it you actually want to calculate? Do you just want a date that is 90 days following the eligibility date? If so, all you need to do is make a real date variable out of pert_Eligibility_Period_Dt, and then add 90 to it.

gen elig_date = daily(pert_Eligible_Period_Dt, "YMD")
format elig_date %td
assert missing(elig_date) == missing(pert_Eligible_Period_Dt)
gen index_date = elig_date + 90
format index_date %td

And if that is the case, what does the part about 365 days after the index visit refer to?

[David Benson]

EDIT: This crossed with Clyde's post.
Jake, I'm having a little trouble understanding what you want. But I took a stab at it--hopefully it will help get you going in the right direction.
My assumption, as you will see in the 2nd code window, is that you had a list of index_dates, and for each date you want to capture the eligibility start and end dates. So I re-purposed your pert_Eligible_Period_Dt date and set that as the particular index_visit date

bysort unique_member_id (pert_Eligible_Period_Dt): gen n = _n
rename unique_member_id id  // just wanted a shorter name.  It makes it easier to list things
order n, after(id)  // placing variable n after id

* NOTE: This is the data you initially uploaded, but it wasn't clear to me
list if inlist(id, 15, 20, 21) & n<=5, sepby(id) abbrev(16) noobs

  +-----------------------------------------------------------------+
  | id   n   pert_Eligible_~t   eligibility_da~2   pert_service_s~c |
  |-----------------------------------------------------------------|
  | 15   1         2013-01-01          19oct2067          09sep2068 |
  | 15   2         2013-02-01          19nov2067          09sep2068 |
  | 15   3         2013-03-01          17dec2067          09sep2068 |
  | 15   4         2013-04-01          17jan2068          09sep2068 |
  | 15   5         2013-05-01          16feb2068          09sep2068 |
  |-----------------------------------------------------------------|
  | 20   1         2015-02-27          14dec2069          26sep2070 |
  | 20   2         2015-03-30          14jan2070          26sep2070 |
  | 20   3         2015-04-29          13feb2070          26sep2070 |
  | 20   4         2015-05-30          16mar2070          26sep2070 |
  | 20   5         2015-06-29          15apr2070          26sep2070 |
  |-----------------------------------------------------------------|
  | 21   1         2012-12-30          17oct2067          06mar2069 |
  | 21   2         2013-01-30          17nov2067          06mar2069 |
  | 21   3         2013-02-27          15dec2067          06mar2069 |
  | 21   4         2013-03-30          15jan2068          06mar2069 |
  | 21   5         2013-04-29          14feb2068          06mar2069 |
  +-----------------------------------------------------------------+

* I assumed that you had a list of index_dates, and for each date you want to capture the eligibility start and end dates
* So I re-purposed your pert_Eligible_Period_Dt date and set that as the index_visit date
rename pert_Eligible_Period_Dt index_visit_str  // the _str at the end is my way of indicating it is a string variable
gen double index_visit = daily( index_visit_str, "YMD")  // converting to a Stata date
gen elegible_start = index_visit - 90
gen elegible_end   = index_visit + 365
format index_visit elegible_start %td
format elegible_end %tdCY-N-D  // just showing some different date display format types

. list id n index_visit elegible_start elegible_end if n<=5, sepby(id) abbrev(16) noobs

  +------------------------------------------------------+
  | id   n   index_visit   elegible_start   elegible_end |
  |------------------------------------------------------|
  | 15   1     01jan2013        03oct2012     2014-01-01 |
  | 15   2     01feb2013        03nov2012     2014-02-01 |
  | 15   3     01mar2013        01dec2012     2014-03-01 |
  | 15   4     01apr2013        01jan2013     2014-04-01 |
  | 15   5     01may2013        31jan2013     2014-05-01 |
  |------------------------------------------------------|
  | 20   1     27feb2015        29nov2014     2016-02-27 |
  | 20   2     30mar2015        30dec2014     2016-03-29 |
  | 20   3     29apr2015        29jan2015     2016-04-28 |
  | 20   4     30may2015        01mar2015     2016-05-29 |
  | 20   5     29jun2015        31mar2015     2016-06-28 |
  |------------------------------------------------------|
  | 21   1     30dec2012        01oct2012     2013-12-30 |
  | 21   2     30jan2013        01nov2012     2014-01-30 |
  | 21   3     27feb2013        29nov2012     2014-02-27 |
  | 21   4     30mar2013        30dec2012     2014-03-30 |
  | 21   5     29apr2013        29jan2013     2014-04-29 |
  |------------------------------------------------------|
  | 23   1     29dec2012        30sep2012     2013-12-29 |
  | 23   2     29jan2013        31oct2012     2014-01-29 |
  | 23   3     26feb2013        28nov2012     2014-02-26 |
  | 23   4     29mar2013        29dec2012     2014-03-29 |
  | 23   5     28apr2013        28jan2013     2014-04-28 |
  +------------------------------------------------------+

[Jake Magel]

I apologize for the unclear request. I added some days to the date variables and that is why they were far off in the future -- this to de-identify the data. I appreciate the help from you both and will be more clear about what I am requesting next time.

Jake