calculated column for frequenct of dates

[ching wong]

Hi all

I have a data set as the following:

ID Date
1 20/03/2018
1 21/03/2018
1 23/03/2018
1 24/03/2018
1 26/03/2018
2 03/05/2018
2 10/05/2018
2 17/05/2018
2 24/05/2018
3 01/06/2018
3 01/07/2018
3 01/08/2018


I would like to find the frequency for each ID. In this case, ID 1 would be daily, ID 2 would be weekly and ID 3 would be monthly.

I tried to find out the different between each date and work out the average but it doesn't work.

Thanks

[William Lisowski]

Here is some code which doesn't answer your question directly, but which demonstrates how to explore the data you gave a sample of to better understand what the various possibilities are.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id date)
1 21263
1 21264
1 21266
1 21267
1 21269
2 21307
2 21314
2 21321
2 21328
3 21336
3 21366
3 21397
end
format %tdDD/NN/CCYY date

bysort id (date) : generate delta = date - date[_n-1]
tab id delta

. tab id delta

           |                         delta
        id |         1          2          7         30         31 |     Total
-----------+-------------------------------------------------------+----------
         1 |         2          2          0          0          0 |         4 
         2 |         0          0          3          0          0 |         3 
         3 |         0          0          0          1          1 |         2 
-----------+-------------------------------------------------------+----------
     Total |         2          2          3          1          1 |         9

[Carole J. Wilson]

**Crossed with William. He correctly uses date-date[n-1]. I've edited my post to correct this.


I don't know how the date variable is stored in your dataset. This is one reason why it is important to use -dataex-. Please read the FAQ 12.2.

The following code gets you part of the way there. I've assumed your Date var is a string. If it is already in Stata format, you can skip the part about generating the new_date variable.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float ID str12 Date
1 "20/03/2018"
1 "21/03/2018"
1 "23/03/2018"
1 "24/03/2018"
1 "26/03/2018"
2 "03/05/2018"
2 "10/05/2018"
2 "17/05/2018"
2 "24/05/2018"
3 "01/06/2018"
3 "01/07/2018"
3 "01/08/2018"
end

gen new_date=date(Date, "DMY")
format new_date %td
bysort ID (Date): gen diff=new_date-new_date[_n-1]

This gives you the following information:

    +------------------------------------+
     | ID         Date    new_date   diff |
     |------------------------------------|
  1. |  1   20/03/2018   20mar2018      1 |
  2. |  1   21/03/2018   21mar2018      2 |
  3. |  1   23/03/2018   23mar2018      1 |
  4. |  1   24/03/2018   24mar2018      2 |
  5. |  1   26/03/2018   26mar2018      . |
     |------------------------------------|
  6. |  2   03/05/2018   03may2018      7 |
  7. |  2   10/05/2018   10may2018      7 |
  8. |  2   17/05/2018   17may2018      7 |
  9. |  2   24/05/2018   24may2018      . |
     |------------------------------------|
 10. |  3   01/06/2018   01jun2018     30 |
 11. |  3   01/07/2018   01jul2018     31 |
 12. |  3   01/08/2018   01aug2018      . |
     +------------------------------------+

I'm not sure what you want to do with this. You can create a new variable, based on the diff variable that distinguishes between daily, weekly, and monthly periods.