Panel data- count number of entries in clinic by patient ID

[Marvin Aliaga]

KEvin.xlsx I have a panel type data-set where the unit of analysis are incidents in a mental health center. So a mental health patient can have many incidents in the year. Some patients also have many entries to the health center (entry id and entry id date). I would like to know how many unique patients have more than one entry in my dataset. I already did this by my n1 variable (command not shown). So if the group have at least two different entry_id (or date), the group is coded as 1 otherwise 0 or missing, What I need now and couldn't do it my self if to count the numbers of entries per patients. Also the time order of my entry. For example I would like to then do analyisis only in patients 2 entry to the clinic. My last two columns is what I would need.

Incident	patient_id	Entry_id	entry_id_date	n1	I need: # of entries	Ineed: Order of entries
0018/15	00034448Y	2411000000	9/26/2014 10:44	1	3	1
0130/15	00034448Y	2411000000	9/26/2014 10:44	1	3	1
1427/15	00034448Y	2411000000	9/26/2014 10:44	1	3	1
2440/15	00034448Y	2411000000	9/26/2014 10:44	1	3	1
4125/15	00034448Y	2411000000	9/26/2014 10:44	1	3	1
4892/15	00034448Y	24110000025	10/26/2014 10:44	1	3	2
5131/15	00034448Y	24111111	12/26/2014 10:44	1	3	3
1335/15	00035149J	1132000000	3/24/2015 1:25		1	1
0602/15	00035566Z	3491000000	5/10/2014 1:44	1	2	1
3349/15	00035566Z	3492000000	8/24/2015 12:31	1	2	2
4797/15	00035566Z	3492000000	8/24/2015 12:31	1	2	2
0059/15	00036470Z	3491000000	8/22/2014 6:13	0	1	1
0670/15	00036470Z	3491000000	8/22/2014 6:13	0	1	1

I would appreciate any help. I am also attaching a excel spreadsheet.

thank you,
Marvin

[Clyde Schechter]

So this is:

clear
input str7 incident str9 patient_id str11 entry_id double entry_id_date_time ///
    byte(n1 ineedofentries ineedorderofentries)
"0018/15" "00034448Y" "2411000000"  1727347440000 1 3 1
"0130/15" "00034448Y" "2411000000"  1727347440000 1 3 1
"1427/15" "00034448Y" "2411000000"  1727347440000 1 3 1
"2440/15" "00034448Y" "2411000000"  1727347440000 1 3 1
"4125/15" "00034448Y" "2411000000"  1727347440000 1 3 1
"4892/15" "00034448Y" "24110000025" 1729939440000 1 3 2
"5131/15" "00034448Y" "24111111"    1735209840000 1 3 3
"1335/15" "00035149J" "1132000000"  1.7427795e+12 . 1 1
"0602/15" "00035566Z" "3491000000"  1715305440000 1 2 1
"3349/15" "00035566Z" "3492000000"  1756038660000 1 2 2
"4797/15" "00035566Z" "3492000000"  1756038660000 1 2 2
"0059/15" "00036470Z" "3491000000"  1724307180000 0 1 1
"0670/15" "00036470Z" "3491000000"  1724307180000 0 1 1
end
format entry_id_date_time %tc

by patient_id entry_id entry_id_date, sort: gen new_entry = 1 if _n == 1
by patient_id (entry_id entry_id_date): gen order_of_entry = sum(new_entry)
by patient_id: gen number_of_entries = order_of_entry[_N]
drop new_entry

assert order_of_entry == ineedorderofentries
assert number_of_entries == ineedofentries

In the future, please be considerate of the people who help you out and post data examples in the way that makes it easiest and quickest to move the data into Stata to work with it. That way is with the -dataex- program written by Robert Picard and Nick Cox, just as I have used it here.. If you do not already have it installed, run -ssc install dataex- and then follow the simple instructions in -help dataex-.

[Marvin Aliaga]

Hi Clyde,

I am extremely sorry about that. I wasn't aware about dataex. I thought I sent an excel file as well on my post so you can import it to Stata quickly. It did not get attached for some reason. Sorry again!

Thank you so much for you help! I have no words to express my gratitude!

Have a nice weekend!

[Clyde Schechter]

You're welcome. Your post does have a link called Kevin.xlsx, and I suppose it leads to a download of an Excel file. But that's not really good either. There are people, including some people who are really frequent responders, who do not use Microsoft Office products. Even among those, who like myself, do use Excel, there is reluctance to download spreadsheets from strangers: spreadsheets can contain active malicious content. Since this is a Stata forum, the thing we obviously all use is Stata. Attaching a Stata data set is a good alternative, but downloading takes longer than just copying the -dataex- output into the do-editor and immediately running it. So I think -dataex- is the way to go unless the necesssary example is a really large data set (in which case attaching the .dta file would be preferrable.)

You have a nice weekend, too!

[Marvin Aliaga]

Will do Clyde! Thanks again, Your take on this was great. First you "tag" the first entry by id and entry_id. Then you sum it to create the order. And finally just used the last number of the order of entry variable to have the total number of entries! This is great!