Aggregate (sum) daily data to weekly data

[Ivan Winpa]

Hi Folks,

I have daily data that i would like to convert to weekly totals. The aggregation variable is transaction_amount and the starting date Varies. Say for instance transactions begun on 12/22/2021 11:36:00 AM, the end of the week should be t+7 where t is the beginning date(12/22/2021 11:36:00 AM). See attached csv file with sample data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str16 transaction_date float transaction_amount
"12/22/2021 11:36"    1000
"12/22/2021 12:09"    1000
"12/22/2021 17:06"  133.44
"12/23/2021 18:17"  133.27
"12/23/2021 18:17"  667.21
"12/23/2021 18:21" 4003.32
"12/23/2021 18:22"  667.22
"12/23/2021 18:23" 1334.44
"12/26/2021 5:25"      100
"12/27/2021 10:56"    5000
"12/27/2021 10:58"    5000
"12/28/2021 15:28"       5
"12/29/2021 13:10"    1200
"12/29/2021 14:02"    2500
"12/30/2021 13:33"    5000
"1/3/2022 10:13"      1000
"1/4/2022 2:09"       1000
"1/4/2022 15:37"    194.61
"1/6/2022 21:00"    676.26
"1/18/2022 10:01"  1046.12
"1/31/2022 12:52"        5
"1/31/2022 12:56"      500
"1/31/2022 12:56"      500
"1/31/2022 12:56"     5000
"1/31/2022 13:07"     1000
"1/31/2022 13:08"   676.76
"1/31/2022 13:08"   677.01
"1/31/2022 13:09"     2421
"1/31/2022 13:10"    678.2
"1/31/2022 13:14"     1000
"1/31/2022 13:14"     1000
"1/31/2022 13:14"   1363.4
"1/31/2022 13:14"  1362.74
"1/31/2022 13:14"  2393.45
"1/31/2022 13:14"   2463.5
"1/31/2022 13:14"   4786.9
"2/8/2022 8:59"       5000
"2/8/2022 12:11"      5000
"2/8/2022 12:12"      1000
"2/8/2022 12:14"      5000
"3/10/2022 13:26"   922.37
"6/23/2022 12:53"  1046.12
"9/13/2022 19:53"        1
"9/15/2022 9:48"     69.95
"9/15/2022 9:57"     69.84
"9/15/2022 10:31"   575.71
"9/15/2022 10:44"   575.71
"9/15/2022 11:12"        1
"9/15/2022 11:15"        1
"9/22/2022 13:50"       20
"9/22/2022 13:51"       20
"9/22/2022 13:51"        2
"9/22/2022 13:56"     5.67
"9/22/2022 14:05"      510
"9/22/2022 14:11"      510
"9/22/2022 14:23"   226.67
"9/22/2022 16:12"   563.23
"9/22/2022 20:49"     1.13
"9/23/2022 17:15"    11.66
"9/23/2022 17:18"    11.66
"9/29/2022 13:15"        1
"9/29/2022 13:19"     1000
"9/29/2022 13:26"     1000
"9/29/2022 13:32"     1000
"9/29/2022 15:01"    21.96
"9/29/2022 15:08"    21.96
"9/29/2022 15:09"    21.96
"10/3/2022 8:44"   2913.71
"10/3/2022 8:48"   2090.02
"10/3/2022 9:04"    498.69
"10/3/2022 12:45"    11.22
"10/3/2022 15:31"      110
"10/3/2022 16:33"      648
"10/4/2022 16:14"    29.93
"10/5/2022 2:12"      5000
"10/5/2022 2:44"      3000
"10/5/2022 2:44"      3000
"10/5/2022 7:44"    613.64
"10/5/2022 8:19"    610.27
"10/5/2022 8:31"     19.88
"10/5/2022 11:06"      300
"10/5/2022 11:29"    21.87
"10/5/2022 12:18"  2977.18
"10/5/2022 12:27"  2977.18
"10/6/2022 11:07"     1.13
"10/6/2022 15:37"   111.98
"10/7/2022 9:23"    145.52
"10/7/2022 11:19"     5.67
"10/8/2022 19:41"    22.17
"10/10/2022 14:22"     200
"10/10/2022 14:26"   19.88
"10/10/2022 14:27"    1.13
"10/10/2022 19:47"   11.06
"10/11/2022 14:07"     100
"10/11/2022 14:20"   11.09
"10/12/2022 9:13"      150
"10/12/2022 11:50"   10.97
"10/12/2022 11:52"    9.88
"10/12/2022 11:56"   11.08
"10/12/2022 11:57"     150
end

[Clyde Schechter]

gen double timestamp = clock(transaction_date, "MDYhm")
assert missing(timestamp) == missing(transaction_date)
format timestamp %tc

gen int week_start = 7*floor((dofc(timestamp)-dofc(timestamp[1]))/7) + dofc(timestamp[1])
format week_start %td
collapse (sum) transaction_amount, by(week_start)

[Ivan Winpa]

Thank you so much Clyde. This was very spot on and helpful. GBU