Having trouble counting unqiue dates by ID variable (_n and _N); Other options?

[Maggie Horn]

Hello,

I am having an issue getting my data coded properly. I have a dataset of patients with encounter dates. Each patient can have many rows of encounters and one or more encounters per date(long). I am trying to create a variable that indicates the order of the encoutners by date and labels each unqiue date 1,2,3.... The issue I cannot resolve is that for patients who have mulitple rows(encounters) for the same date, I want this indicated with the same number. This dataset is quite large and many vairables other than displayed.

So far i have done (which is not working):

bysort ID visit_date: generate datevisit_count1 = _n
bysort ID visit_date: generate datevisit_count2 = _N
bysort ID : generate datevisit_count3 = _N

I am trying to get the data to display such as i have input for column 6 (What I am trying to accomplish).

Any help would be appreciated.

Thank you,

Maggie

ID	visit_date	datevisit_count1	datevisit_count2	datevisit_count3	What I am trying to accomplish
1	11/23/2015	1	1	23	1
1	1/3/2016	2	1	23	2
1	1/4/2016	3	1	23	3
1	1/14/2016	4	1	23	4
1	1/23/2016	5	1	23	5
1	1/27/2016	6	1	23	6
1	1/29/2016	7	1	23	7
1	2/2/2016	8	2	23	8
1	2/2/2016	9	2	23	8
1	2/16/2016	10	1	23	9
1	2/25/2016	11	1	23	10
1	5/7/2016	12	1	23	11
1	5/15/2016	13	1	23	12
1	6/2/2016	14	1	23	13
1	6/13/2016	15	1	23	14
1	6/14/2016	16	1	23	15
1	6/17/2016	17	1	23	16
1	6/28/2016	18	1	23	17
1	7/5/2016	19	1	23	18
1	7/13/2016	20	1	23	19
1	7/27/2016	21	1	23	20
1	8/2/2016	22	1	23	21
1	11/14/2016	23	1	23	22

[Clyde Schechter]

The first obstacle to overcome is you need a real Stata internal format date variable. It may be that you already have one--your data tableau does not provide that crucial piece of information. I'm assuming in the code below that what you have is just a string that is readable as a date by human eyes, and I convert it into a new variable, sif_date. If you actually have a real Stata date variable, skip that part and also use visit_date in place of sif_date in the rest of the code.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str10 visit_date byte(datevisit_count1 datevisit_count2 datevisit_count3)
1 "11/23/2015"  1 1 23
1 "1/3/2016"    2 1 23
1 "1/4/2016"    3 1 23
1 "1/14/2016"   4 1 23
1 "1/23/2016"   5 1 23
1 "1/27/2016"   6 1 23
1 "1/29/2016"   7 1 23
1 "2/2/2016"    8 2 23
1 "2/2/2016"    9 2 23
1 "2/16/2016"  10 1 23
1 "2/25/2016"  11 1 23
1 "5/7/2016"   12 1 23
1 "5/15/2016"  13 1 23
1 "6/2/2016"   14 1 23
1 "6/13/2016"  15 1 23
1 "6/14/2016"  16 1 23
1 "6/17/2016"  17 1 23
1 "6/28/2016"  18 1 23
1 "7/5/2016"   19 1 23
1 "7/13/2016"  20 1 23
1 "7/27/2016"  21 1 23
1 "8/2/2016"   22 1 23
1 "11/14/2016" 23 1 23
end

gen sif_date = daily(visit_date, "MDY")
assert missing(sif_date) == missing(visit_date)
format sif_date %td
drop visit_date

by id sif_date, sort: gen wanted = (_n == 1)
by id (sif_date): replace wanted = sum(wanted)

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data. (For example, had you used -dataex-, I would not have had to guess whether your date variable needed to be reworked.)

When asking for help with code, always show example data. When showing example data, always use -dataex-.