Collapsing milliseconds data in seconds

[Eli Hadad]

Hi, i have a question in collapsing data. In the data sample below the header
is the timestamp in milliseconds and the closing price of stock3 and stock4.
i would like to collapse the milliseconds in seconds and the prices by the mean value.
Any help appreciated.
Thanks in advance.
Eli Hadad JUnior

* Example generated by -dataex-. For more info, type help dataex
clear
input double timestamp float(stock3 stock4)
1982916613607 33.85 30.58
1982916613610 33.85 30.58
1982916613620 33.85 30.58
1982916613633 33.85 30.57
1982916613633 33.85 30.57
1982916613633 33.85 30.58
1982916613637 33.85 30.57
1982916613640 33.85 30.57
1982916613643 33.85 30.57
1982916613643 33.85 30.57
1982916613643 33.85 30.57
1982916613647 33.85 30.57
1982916613647 33.85 30.58
1982916613647 33.85 30.58
1982916613647 33.85 30.57
1982916613647 33.85 30.57
1982916613667 33.85 30.57
1982916613670 33.85 30.57
1982916613670 33.85 30.57
1982916613673 33.85 30.57
1982916613677 33.85 30.57
1982916613677 33.85 30.57
1982916613677 33.85 30.57
1982916613680 33.85 30.56
1982916613680 33.85 30.56
1982916613683 33.85 30.56
1982916613687 33.85 30.57
1982916613687 33.85 30.56
1982916613690 33.85 30.56
1982916613690 33.85 30.56
1982916613693 33.85 30.56
1982916613697 33.85 30.56
1982916613697 33.85 30.56
1982916613703 33.85 30.56
1982916613707 33.85 30.55
1982916613710 33.85 30.55
1982916613710 33.85 30.55
1982916613717 33.85 30.55
1982916613730 33.85 30.55
1982916613733 33.85 30.55
1982916613753 33.85 30.58
1982916613753 33.85 30.58
1982916613757 33.85 30.58
1982916613757 33.85 30.57
1982916613763 33.85 30.56
1982916613763 33.85 30.55
1982916613767 33.85 30.55
1982916613800 33.79 30.55
1982916613803 33.79 30.57
1982916613807 33.79 30.55
1982916613810 33.79 30.55
1982916613810 33.79 30.55
1982916613813 33.79 30.55
1982916613817 33.79 30.55
1982916613827 33.79 30.55
1982916613890 33.79 30.55
1982916613893 33.79 30.55
1982916613897 33.79  30.6
1982916613897 33.79  30.6
1982916613897 33.79 30.59
1982916613897 33.79  30.6
1982916613897 33.79  30.6
1982916613900 33.79 30.58
1982916613910 33.79 30.57
1982916613917 33.76 30.57
1982916613937 33.76 30.57
1982916613963 33.76 30.57
1982916613963 33.76 30.57
1982916613963 33.76 30.57
1982916614020 33.76 30.57
1982916614023 33.79 30.57
1982916614023 33.79 30.57
1982916614027 33.79 30.57
1982916614030 33.81 30.57
1982916614030 33.81 30.57
1982916614030 33.81 30.57
1982916614037 33.81 30.57
1982916614050 33.81 30.56
1982916614067 33.82 30.57
1982916614073 33.82 30.57
1982916614077 33.82 30.57
end
format %tcCCYY_NN_DD_HH_MM_SS.sss timestamp

[Andrew Musau]

One approach could be to create a string variable specifying format "%tc" and then collapse.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double timestamp float(stock3 stock4)
1982916613607 33.85 30.58
1982916613610 33.85 30.58
1982916613620 33.85 30.58
1982916613633 33.85 30.57
1982916613633 33.85 30.57
1982916613633 33.85 30.58
1982916613637 33.85 30.57
1982916613640 33.85 30.57
1982916613643 33.85 30.57
1982916613643 33.85 30.57
1982916613643 33.85 30.57
1982916613647 33.85 30.57
1982916613647 33.85 30.58
1982916613647 33.85 30.58
1982916613647 33.85 30.57
1982916613647 33.85 30.57
1982916613667 33.85 30.57
1982916613670 33.85 30.57
1982916613670 33.85 30.57
1982916613673 33.85 30.57
1982916613677 33.85 30.57
1982916613677 33.85 30.57
1982916613677 33.85 30.57
1982916613680 33.85 30.56
1982916613680 33.85 30.56
1982916613683 33.85 30.56
1982916613687 33.85 30.57
1982916613687 33.85 30.56
1982916613690 33.85 30.56
1982916613690 33.85 30.56
1982916613693 33.85 30.56
1982916613697 33.85 30.56
1982916613697 33.85 30.56
1982916613703 33.85 30.56
1982916613707 33.85 30.55
1982916613710 33.85 30.55
1982916613710 33.85 30.55
1982916613717 33.85 30.55
1982916613730 33.85 30.55
1982916613733 33.85 30.55
1982916613753 33.85 30.58
1982916613753 33.85 30.58
1982916613757 33.85 30.58
1982916613757 33.85 30.57
1982916613763 33.85 30.56
1982916613763 33.85 30.55
1982916613767 33.85 30.55
1982916613800 33.79 30.55
1982916613803 33.79 30.57
1982916613807 33.79 30.55
1982916613810 33.79 30.55
1982916613810 33.79 30.55
1982916613813 33.79 30.55
1982916613817 33.79 30.55
1982916613827 33.79 30.55
1982916613890 33.79 30.55
1982916613893 33.79 30.55
1982916613897 33.79  30.6
1982916613897 33.79  30.6
1982916613897 33.79 30.59
1982916613897 33.79  30.6
1982916613897 33.79  30.6
1982916613900 33.79 30.58
1982916613910 33.79 30.57
1982916613917 33.76 30.57
1982916613937 33.76 30.57
1982916613963 33.76 30.57
1982916613963 33.76 30.57
1982916613963 33.76 30.57
1982916614020 33.76 30.57
1982916614023 33.79 30.57
1982916614023 33.79 30.57
1982916614027 33.79 30.57
1982916614030 33.81 30.57
1982916614030 33.81 30.57
1982916614030 33.81 30.57
1982916614037 33.81 30.57
1982916614050 33.81 30.56
1982916614067 33.82 30.57
1982916614073 33.82 30.57
1982916614077 33.82 30.57
end
format %tcCCYY_NN_DD_HH_MM_SS.sss timestamp

gen strtc= string(timestamp, "%tc")
collapse timestamp stock3 stock4, by(strtc)

Res.:

. l

     +--------------------------------------------------------------------+
     |              strtc                 timestamp     stock3     stock4 |
     |--------------------------------------------------------------------|
  1. | 01nov2022 10:10:13   2022 11 01 10 10 13.744   33.82869   30.56696 |
  2. | 01nov2022 10:10:14   2022 11 01 10 10 14.040   33.80333   30.56917 |
     +--------------------------------------------------------------------+

[Eli Hadad]

Thanks for the help. In fact i don`t understand the mask %tcCCYY_NN_DD_HH_MM_SS.sss. would you please give an explanation?

[Andrew Musau]

In fact i don`t understand the mask %tcCCYY_NN_DD_HH_MM_SS.sss. would you please give an explanation?

Century Year [CCYY] Month (numerical) [NN] Day [DD] Hour [HH] Minute [MM] Second including fractional part [SS.sss]

See

help datetime

and

help format