calculating number of visits in a year?

[Branu Ags]

Hi,

I am trying to calculate how many PCP visits a patient have in a year. The dataset has enrolid (unique id), PCP visits (yes or no, 1 or 0), and the svcdate (date of service). Each enrolid could have multiple observations/ rows depending on how many services they used. Also, each enrolid might have multiple services on the same svcdate. Below is how the data looks. (The original dataset has 6.8M rows for 20,000 enrolid, and I am trying to generate variables so that in the end I am left with 20K rows, one for each enrol id)

input double enrolid float pcp long svcdate
350802 0 20616
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 1 20688
1315601 1 20481
1315601 1 20481
1315601 0 20488
1315601 0 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488

format %tdD_m_Y svcdate
[/CODE]

I want to now generate 2 variables:
1) Did the patient have any pcp visit that year?
For that, I used the following command:
by enrolid, sort: egen anypcpvisit2016 = max(pcp) //If any visit with the pcp during the year

Does the above code seem reasonable?

2) Now I want to know how many pcp visits they had. All the pcp visits that they had on the same service date should be counted as one visit. I think I am stuck at this point. As you can see above in the dataset, the patient 1(350802) had 1 pcp visit, but the other patient (1315601) had 2 PCP visits on svcdate 20481 and then 5 on svcdate 20488. Thus the output I want is that the patient (1315601) had a total of 2 visits in the year but the code I wrote below gives me the answer as 7.

I wrote the command as follows:
by enrolid, sort: egen numberpcpvisit2016 = sum(pcp) //No of pcp visits during the year

May be I need to sort by enrolid AND svcdate, but don't know how to do that.

Any suggestions?
That you.

[Nick Cox]

Thanks for your data example. Here's a revised version, because the code you gave will evoke an error. I have no idea what pcp means but it doesn't seem to matter.

clear 
input double enrolid float pcp long svcdate
350802 0 20616
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 0 20688
350802 1 20688
1315601 1 20481
1315601 1 20481
1315601 0 20488
1315601 0 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488
1315601 1 20488
end 

format %tdD_m_Y svcdate

Taking your questions in turn:

1) Did the patient have any pcp visit that year?

For that, I used the following command:

by enrolid, sort: egen anypcpvisit2016 = max(pcp)

That is a correct answer if and only if your dataset refers only to one year. Otherwise you need a more general approach, such as

gen year = year(svcdate) 

bysort enrolid year (pcp) : gen max = pcp[_N]

2) How many pcp visits did each patient have? All pcp visits on the same service date should be counted as one.

Here's one way to do it.

bysort enrolid year svcdate (pcp) : gen tocount = cond(_n == _N, pcp[_N], 0)
bysort enrolid year : replace tocount = sum(tocount) 
bysort enrolid year : replace tocount = tocount[_N]

That code allows for some visits on the same date being pcp and some not, which is true for each patient in the data example. We sort on pcp within identifier, year, and service date and pick the last value as summary. The last lines could be replaced with an egen statement (shorter code that takes longer).

. list, sepby(enrolid) 

     +--------------------------------------------------+
     | enrolid   pcp     svcdate   year   max   tocount |
     |--------------------------------------------------|
  1. |  350802     0   11 Jun 16   2016     1         1 |
  2. |  350802     0   22 Aug 16   2016     1         1 |
  3. |  350802     0   22 Aug 16   2016     1         1 |
  4. |  350802     0   22 Aug 16   2016     1         1 |
  5. |  350802     0   22 Aug 16   2016     1         1 |
  6. |  350802     0   22 Aug 16   2016     1         1 |
  7. |  350802     0   22 Aug 16   2016     1         1 |
  8. |  350802     0   22 Aug 16   2016     1         1 |
  9. |  350802     0   22 Aug 16   2016     1         1 |
 10. |  350802     0   22 Aug 16   2016     1         1 |
 11. |  350802     0   22 Aug 16   2016     1         1 |
 12. |  350802     1   22 Aug 16   2016     1         1 |
     |--------------------------------------------------|
 13. | 1315601     1   28 Jan 16   2016     1         2 |
 14. | 1315601     1   28 Jan 16   2016     1         2 |
 15. | 1315601     0   04 Feb 16   2016     1         2 |
 16. | 1315601     0   04 Feb 16   2016     1         2 |
 17. | 1315601     1   04 Feb 16   2016     1         2 |
 18. | 1315601     1   04 Feb 16   2016     1         2 |
 19. | 1315601     1   04 Feb 16   2016     1         2 |
 20. | 1315601     1   04 Feb 16   2016     1         2 |
 21. | 1315601     1   04 Feb 16   2016     1         2 |
     +--------------------------------------------------+

.

[Branu Ags]

Thank you. This worked!