Counting Number of Observations by Date and Another Condition

[Juliet Oriaifo]

Hello everyone,

I am wondering if anyone could help to create a command that automates counting the number of observations based on date. Basically, I would like the NewVar to be created based on the Cusip and Date, but the NewVar will be created by the number of same cusip up until a particular day and month of the same year, not past a day and month of the same year. Below, I have an example of how it should look like:

ID	Cusip	Date	NewVar
1	100110	01jan2014	0
2	100110	04apr2014	1
3	100110	02may2014	2
4	201212	21mar2015	0
5	201212	03may2015	1
6	201212	20aug2015	2
7	201212	20aug2015	2

As you see, for ID#2 row, the NewVar is a total of 1 (the date before its own date), while ID#3 accounts for obs of 2 in the NewVar (the two dates before its own date).

[Andrew Musau]

bys Cusip (Date): gen wanted=sum(date!=date[_n+1])-1

[Juliet Oriaifo]

Andrew. This does not seem to be working.

[Andrew Musau]

The only thing I see that I did not take into account is the year. If the following does not work, please read FAQ Advice #12 and present a data example using dataex with examples of what you expect across multiple years for a given ID. Otherwise, stating that the code does not work is not very informative. The code assumes that your date variable contains SIF values (i.e., a date variable recognized by Stata). This can only be apparent if you present an example as outlined in the FAQs.

gen Year = year(Date)
bys Cusip Year (Date): gen wanted=sum(date!=date[_n+1])-1

[Juliet Oriaifo]

Andrew. This is an example of what the second command is producing:

ID	Cusip	Date	Wanted
1	100110	01jan2014	0
2	100110	04apr2014	0
3	100110	02may2014	0
4	201212	21mar2015	0
5	201212	03may2015	0
6	201212	20aug2015	-1
7	201212	20aug2015	-1

[Andrew Musau]

Re-read #4. Below, I recreate your example data in #5 and run the code. As you can see, I get different results which suggests that there is something in your data setup that is not right. I cannot advise on why you get a different result without seeing a sample of your data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ID long Cusip float Date
1 100110 19724
2 100110 19817
3 100110 19845
4 201212 20168
5 201212 20211
6 201212 20320
7 201212 20320
end
format %td Date
 
gen Year = year(Date)
bys Cusip Year (Date): gen wanted=sum(Date!=Date[_n-1])-1

Res.:

. l, sepby(Cusip)

     +-----------------------------------------+
     | ID    Cusip        Date   Year   wanted |
     |-----------------------------------------|
  1. |  1   100110   01jan2014   2014        0 |
  2. |  2   100110   04apr2014   2014        1 |
  3. |  3   100110   02may2014   2014        2 |
     |-----------------------------------------|
  4. |  4   201212   21mar2015   2015        0 |
  5. |  5   201212   03may2015   2015        1 |
  6. |  6   201212   20aug2015   2015        2 |
  7. |  7   201212   20aug2015   2015        2 |
     +-----------------------------------------+

[Juliet Oriaifo]

Thank you. This is what I used and it worked: bys cusip year (date): gen wanted=sum( date!=year[_n-1])-1

[Nick Cox]

I guess you used the code in #6 and the difference in #7 was a typo.

[Juliet Oriaifo]

Yes exactly.