Never mind, I solved it. Here -ndays- is a new variable
for -days-; I get the last day of quarter (-ldoq-) for
each date and use that to determine if a given date is
last for the quarter (-loq-). Maybe there's an even
cleaner way to manage the duplicated observations?
gen ndays=days
bys ID (date): gen ldoq=date+(90-mod(date-date[1]+1,90))
bys ID (date): gen loq=date<ldoq&ldoq<=date[_n+1]
gen overlap=date+days-ldoq
gen dup=overlap>0&loq
expand 2 if dup
bys ID date : replace ndays=ldoq-date if _n==1&dup
bys ID date : replace date=ldoq+1 if _n==2&dup
bys ID date : replace ndays=ldoq-date if _n==1&dup
bys ID date : replace ndays=overlap if _n==2&dup
cheers,
Jeph
Jeph Herrin wrote:
Listers,
I'm hoping for some insight into a solution to the following
programming problem.
For 170k patients (ID), I have the dates they filled prescriptions
(-filldate-) and the number of -days- each prescription covers (eg,
the number of daily pills):
ID filldate days
1 10JAN01 30
1 20FEB01 14
1 03MAR01 45
1 05MAY01 30
.
.
2 etc
2
2
.
.
.
The problem is that I need to figure how many total -days- they
are covered out of each 90 day quarter. So in the above, the first
patient starts on 10JAN01, so the first 90 day quarter ends on
10APR01. That means I need to break the 3rd record (03MAR01) into
two records, 38 days for the first quarter and 7 days for the second
quarter. I have to do this for all four 90 day quarters starting
10JAN01, and I have to do for every patient (and they all have
different start dates).
What I'd like to do is insert a new record at the start of each
quarter UNLESS they already have a record for that date. Then
the last -filldate- for each quarter would be split to form the
new record.
However, all of my attempts end up looking hopelessly complex
and hence unreliable, and as I so often discover elegant solutions
on this list I thought I'd fish for one here. Any ideas would
be welcome.
cheers,
Jeph
--
Jeph Herrin
[email protected]
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
