Dear All

I have panel data that includes year, quarter, month and week, firm, industry, and (firm) profit
The year ranges between 1981 to 2020
The quarter takes values from 1 to 4 to represent each quarter within the year
The month takes values from 1 to 3 to represent each month within the quarter
The week takes values from 1 to 4 to represent each week within the month

I want to convert this panel data into time-series data representing the mean profits of firms. Most importantly, these means should be calculated in an increasing rolling window fashion such that the mean of profits in week 1 represents the mean of profit in week 1, the mean of profits in week 2 represents the mean of profits in week 1 and 2, the mean of profits in week 3 represents the mean of profits in week 1,2 and 3; and so on.

I did the following:

Code:
*generating "series" to take values from 1 to 12 representing data available at each week of the quarter
gen real_month = 3*(quarter-1) + month
gen quarter_date = qofd(mdy(real_month, 1, year))
format quarter_date %tq
gen series = 4*(month-1) + week

* generating increasing window rolling means for the weekly profit data within each quarter of the year
rangestat (mean) profit, interval(series 0 0) by(quarter_date ) casewise 

* keep only one profit_mean observation for each series in the quarter (i.e. each week within the quarter)
duplicates drop quarter_date series profit_mean, force

sort series quarter_date
xtset series quarter_date, quarterly
Now my data becomes quarterly time-series while including mean profit at each week of the quarter. If I want, for example, to plot the time-series of profit when it was "available" in week 1 of any quarter, I will do the following:

Code:
tsline profit_mean if series==1
If I instead want to plot it as it is available in week 7 for example, I will do the following:

Code:
tsline profit_mean if series==7

And so on. In other words, any analysis I do will then be conditioned on the series as it represents the point (week) when this data was available.
At this point, you probably see my aim. I want to have profit time series as they are available in real-time each week.


My problem:

1- First, I hope the code I have above will correctly produce what I have described?! I will be thankful if someone can confirm this.

2- Second, I also want to create time-series of profits in an identical way but these time series should be for each industry. Specifically, the code above produces a time-series of profit calculated from ALL FIRMS' profit in the cross-section (at different weeks of the quarter). Now, if I have 10 industries, I would like to have 10 profit time-series variables in the same way as above (i.e. calculated at different weeks of the quarter).

I really hope someone can help with that

My data looks like:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long firmID byte industry float(year quarter month week) long profit
  5709 30 1981 1 2 3   354240
 10618 20 1981 1 2 3    77657
  2497 20 1981 1 2 4    51141
  8577 55 1981 1 3 1    99506
  7922 25 1981 1 3 1   130866
  6829 25 1981 1 3 1   205798
  1410 20 1981 1 3 2    63145
 11259 30 1981 1 3 2   554184
  4598 20 1981 1 3 3   152512
  4990 25 1981 1 3 3   125319
  5667 10 1981 1 3 3    93135
  7116 25 1981 1 3 3   205362
  7138 60 1981 1 3 3    57686
  9538 30 1981 1 3 3    90739
  6307 25 1981 1 3 3  4441594
  9818 25 1981 1 3 3  1274557
  3813 25 1981 1 3 3  1428899
  5018 20 1981 1 3 4   497461
  1913 15 1981 1 3 4   152901
  9906 55 1981 1 3 4   120843
  8579 25 1981 1 3 4    40898
  9599 45 1981 1 3 4     3545
  8151 10 1981 2 1 1   349232
  2817 20 1981 2 1 2  2190700
 10984 50 1981 2 1 2   534012
 11264 30 1981 2 1 2   489653
 10507 55 1981 2 1 3   555374
  1045 20 1981 2 1 3   952544
  6178 55 1981 2 1 3    87216
  1161 45 1981 2 1 2    80318
  3036 50 1981 2 1 3    81209
  5742 55 1981 2 1 3   609402
  4029 55 1981 2 1 3   554203
  3980 50 1981 2 1 3   235066
  9846 55 1981 2 1 3   908514
 14912 55 1981 2 1 3   908514
  7366 55 1981 2 1 3   588403
 10601 55 1981 2 1 3   108232
  4798 55 1981 2 1 2   604031
  5237 20 1981 2 1 3    14835
  5256 20 1981 2 1 3   182286
  7938 10 1981 2 1 3   125411
  4517 55 1981 2 1 2   604031
  7985 20 1981 2 1 3   435200
  7993 55 1981 2 1 3   111131
  8215 15 1981 2 1 2   963700
  9828 55 1981 2 1 3   176961
  8272 55 1981 2 1 2   775281
  4988 50 1981 2 1 3   304738
 10867 20 1981 2 1 3  1511519
 11161 45 1981 2 1 3     4716
 11185 20 1981 2 1 3    30813
 11555 55 1981 2 1 2   140134
 65095 55 1981 2 1 2   140134
 66591 55 1981 2 1 3   387850
 12612 55 1981 2 1 4   238925
 10860 55 1981 2 1 4   240450
  1440 55 1981 2 1 3  1099957
  3814 55 1981 2 1 4   238925
  7437 55 1981 2 1 4    95374
  1209 15 1981 2 1 3   413030
  1743 20 1981 2 1 4    90622
  2137 50 1981 2 1 4  1406957
  3170 30 1981 2 1 4  1326361
  3439 55 1981 2 1 4   822618
 13948 55 1981 2 1 4   822618
  7241 35 1981 2 1 4   469909
 12564 55 1981 2 1 4   229625
  3851 20 1981 2 1 4   881574
  4091 20 1981 2 1 4    43288
  4093 55 1981 2 1 4   498256
  2783 55 1981 2 1 4   349428
  4331 55 1981 2 1 4    22149
  8099 55 1981 2 1 4   308837
  5903 10 1981 2 1 4  1512000
  7260 50 1981 2 1 3   108026
  6867 55 1981 2 1 4    72538
  7017 10 1981 2 1 4  2281727
  7585 45 1981 2 1 4   858897
  7875 25 1981 2 1 4    37117
  8810 55 1981 2 1 4   982938
 12749 55 1981 2 1 4   982938
  8762 30 1981 2 1 4  2949001
  1075 55 1981 2 1 3   191582
  1742 55 1981 2 1 3   191582
  8455 55 1981 2 1 4   314529
145348 55 1981 2 1 4   314529
 23465 55 1981 2 1 4   149435
  9698 20 1981 2 1 3    17389
  9324 55 1981 2 1 4     5764
 11012 15 1981 2 1 3   100253
 31596 55 1981 2 1 4   918607
  1104 20 1981 2 1 4     9966
 15448 55 1981 2 1 4   185578
  3580 20 1981 2 1 4   374885
  2991 10 1981 2 1 4 11352000
  3897 55 1981 2 1 4   497396
 65089 55 1981 2 1 4   497396
  3413 55 1981 2 1 4  1221300
  4241 55 1981 2 1 4    54400
end
Thank you
Mike