Subtracting time variables

[Fanetti Mazakura]

Hello everyone!

Here is a chunk of the dataset that I am using:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long personid double permno int trandate float month
169 10107 14651 481
169 10107 14906 489
169 10107 14906 489
169 10107 14906 489
169 10107 14941 490
169 10107 15095 495
169 10107 15656 514
169 10107 15656 514
169 10107 15656 514
169 10107 15750 517
169 10107 15750 517
169 10107 15750 517
169 10107 15838 520
169 10107 15845 520
169 10107 15845 520
169 10107 15950 524
169 10107 15951 524
169 10107 16188 531
169 10107 16328 536
169 10107 16749 550
169 10107 16749 550
169 10107 16749 550
169 10107 16757 550
169 10107 16757 550
169 10107 16757 550
169 10107 16758 550
169 10107 16763 550
169 10107 16763 550
169 10107 16834 553
169 10107 16840 553
169 10107 16840 553
169 10107 16840 553
169 10107 16840 553
end
format %d trandate
format %tmMon_CCYY month

I am trying to compute the number of days per month a person is trading (Without counting the same day more than once). For instance, in the above example, he/she has placed two trades on November 23, one on November 18, three on November 17 and three trades on November 9. I need to get the total of 4 days in November 2005.

Furthermore, I need to compute the number of days he/she takes to complete the transaction in the month, equal to the n-th day since he/she has made the first transaction in the particular month. In the above example this is equal to (November 23 - November 9) + 1 = 15 days (Last transaction date minus the first transaction date in a month + 1).

I hope you understood what I am trying to achieve. I would greatly appreciate any advice! Thank you very much!

Cheers!

[Robert Picard]

You can do this by grouping observations together and using explicit subscripting to target the first and last observation within each by-group.

sort personid month trandate
by personid month trandate: gen trade1 = _n == 1
by personid month: egen days_trading = total(trade1)
by personid month: gen duration = trandate[_N]-trandate[1]+1

The first command tags the first observation within the by-group. The second calculates the tag count within the group. The last command subtracts the first date from the last date in the by-group to find the duration.

[Clyde Schechter]

//    CALCULATE DURATION OF TRADING IN MONTH
by personid month (trandate), sort: gen duration = trandate[_N] - trandate[1] + 1

//    CALCULATE NUMBER OF DISTINCT DAYS OF TRADING IN MONTH
by personid month (trandate): gen n_days = sum(trandate != trandate[_n-1])
by personid month (trandate): replace n_days = n_days[_N]

Added: Crossed with #2, which offers a nearly identical solution.

[Fanetti Mazakura]

Thanks, guys! I'm really grateful for your help!

Best regards,
Fanetti