Ratio of two variables over three years

[Tabitha Jaeger]

I would like to run a regression with a variable that is the ratio of green Joint ventures (gJV) from overall green Alliances (gSA) in my sample over a time frame of three years. I have tried to use the

rolling

command with this code

rolling ratio=(gJV/gSA), window(3) stepsize(3): summarize ratio, detail

. I would like to have a window of three years and have every year appear only once (hence the stepsize would be 3 as well).
This command does not work and

variable ratio not found, an error occurred when rolling executed summarize r(111);

appears.
I am very unsure how to perform this calculation and whether it is possible to calculate the moving averages with the help of

rangestat

or other commands and then simply create ratios of these means over three years?
I would very much appreciate any help on that matter.

[Nick Cox]

You can and perhaps should calculate the ratio directly

gen ratio = gJV / gSA

Apart from being illegal, the command you tried doesn't have a clear goal.

1. summarize, detail leaves behind many statistics as results, so which do you want to use?

2. What should appear in a regression?

3. Do you have yearly data, in which case 3 data points are too few for any serious regression; or do you have monthly, weekly, daily, whatever, data, in which case your window is more complicated.


Ratios can be very badly behaved, depending on numerator and denominator! Also, in general the mean of a ratio is not equal to the ratio of the means of the components, so what makes sense needs considerable thought.

[Tabitha Jaeger]

Thank you for your answer. I have alliance and JV deals from 2005-2020 and would like to do a regression of this ratio of JV from alliances on firm value so ratio is my IV and tobin´s q my DV. However, I would not like to look at the ratio of JV from alliances within one year but I would like to receive one value over a time frame of three years as I have very limited observations.

If I use the command you proposed

gen ratio = gJV / gSA

, can I use a rolling regression over three years with the generated ratio then?


I found this command

. webuse lutkepohl2, clear
. tsset qtr
. rolling ratio=(r(mean)/r(p50)), window(10): summarize inc, detail
. list in 1/10

and thought I could use the rolling command for calculating my ratio over three years as well.

[Nick Cox]

Thanks for the details. I don't work in your field and don't really understand your reply, so I will leave this open. I don't see that you answered any of my questions!

[Tabitha Jaeger]

I am sorry, I tried answering them but I am very unsure myself.
1. I do not need any of the summarize statistics but Stata told me to add

: command

after the rolling command so I put summarize as I found it in multiple examples. I aim to receive values for the ratios over three years and do not need anything else.
2. The regression should have the ratio of JV and alliances over three-year windows as the IV and tobin´s Q as the DV.
3. I do have yearly data, yes. However, I do not intend to only have three data points but to summarize all data points fro three-year intervals as one data point.

[Nick Cox]

I am still foggy on what you want to do, as you don't seem clear yourself (which is understandable, but doesn't make answering easier), there is no data example here, and I I don't work in this field. But no-one else has chimed in, so let's see what we can do.

As I understand it, you want a rolling regression for which you have an outcome (you say DV) and two predictors, which need to be in variables you have before you can do the regression.

rolling requires that you tsset your data first, presumably if terms of an identifier and a time variable. If that's not possible, rolling isn't possible, but there may be other choices.

How many observations are available for a typical regression? With 2 predictors, I wouldn't feel comfortable unless there were many more than 3.

[Tabitha Jaeger]

Thank you for answering! There are 9,007 observations.
I am aware of the rolling command but still struggle to create my predictor which shall be the ratio of two other variables over a time frame of three years because I am dealing with green alliances and green joint ventures and there are very little observations considered green, I decided to not create simply a ratio of greenJV from green SA for every fyear in the sample (2005-2020) but to summarize the number of green JV over three year windows, do the same for green alliances and then take the ratio of those three-year sums. In order to do that, I wanted to use the

rolling

command and create a ratio over rolling windows of 3 and with stepsize of 3 years. That was my idea behind it but it seems not possible to do it like that. Do you understand a little better what I am trying to do?

[Nick Cox]

Sorry, no.