Create new variable: from quarterly survival to yearly survival

[Laura Hill]

Dear all,

I'm trying to create new variables on a yearly basis, whereas currently I have a panel dataset on a quarterly basis.
More specifically for each firm (BvdIdNumber) I have a variable called Quarter which ranges from 0 to 40 and a variable failure (FirmFailure3), which equals 1 if failure occured.
Once a failure has occured, there are no subsequent observations for that firm (see dataex below as an example).

I have already created a variable for Years, meaning if quarter = 1-4 than year = 1, if quarter 5-8 than year = 2, etc.
However, I cannot figure out how to create a failure variable on a yearly basis, based of the other failure variable.
Specifically, in the year it failed, I would need all values to be equal to 1, not just the last one.

I might be overlooking a very straightforward answer, but after some searching I just can't seem to come up with the solution.
Thanks in advance for your help!

Best regards,
Laura

fyi: I am using Stata 15/MP on my work pc but I have access to Stata 18/SE on my personal pc if needed.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 BvdIdNumber byte(Quarter FirmFailure3) float Years
"AT9010119613"  1 0  1
"AT9010119613"  2 0  1
"AT9010119613"  3 0  1
"AT9010119613"  4 0  1
"AT9010119613"  5 0  2
"AT9010119613"  6 0  2
"AT9010119613"  7 0  2
"AT9010119613"  8 0  2
"AT9010119613"  9 0  3
"AT9010119613" 10 0  3
"AT9010119613" 11 0  3
"AT9010119613" 12 0  3
"AT9010119613" 13 0  4
"AT9010119613" 14 0  4
"AT9010119613" 15 0  4
"AT9010119613" 16 0  4
"AT9010119613" 17 0  5
"AT9010119613" 18 0  5
"AT9010119613" 19 0  5
"AT9010119613" 20 0  5
"AT9010119613" 21 0  6
"AT9010119613" 22 0  6
"AT9010119613" 23 0  6
"AT9010119613" 24 0  6
"AT9010119613" 25 0  7
"AT9010119613" 26 0  7
"AT9010119613" 27 0  7
"AT9010119613" 28 0  7
"AT9010119613" 29 0  8
"AT9010119613" 30 0  8
"AT9010119613" 31 0  8
"AT9010119613" 32 0  8
"AT9010119613" 33 0  9
"AT9010119613" 34 1  9
"AT9010121827"  1 0  1
"AT9010121827"  2 0  1
"AT9010121827"  3 0  1
"AT9010121827"  4 0  1
"AT9010121827"  5 0  2
"AT9010121827"  6 0  2
"AT9010121827"  7 0  2
"AT9010121827"  8 0  2
"AT9010121827"  9 0  3
"AT9010121827" 10 0  3
"AT9010121827" 11 0  3
"AT9010121827" 12 0  3
"AT9010121827" 13 0  4
"AT9010121827" 14 0  4
"AT9010121827" 15 0  4
"AT9010121827" 16 0  4
"AT9010121827" 17 0  5
"AT9010121827" 18 0  5
"AT9010121827" 19 1  5
end

[Clyde Schechter]

Well, I don't understand your question. Why are you concerned with changing the values in each quarter if what you're trying to do is convert this to yearly data. I would just forget about the quarters:

collapse (max) FirmFailure3, by(BvdIdNumber Years)

What am I missing here?

[Laura Hill]

Clyde Schechter, thanks for your reply.

The above command indeed does what I would like to do, but would it be possible to do the following without using collapse.
The reason being is that I am doing my analyses (cox model) on a quarterly basis, but for my descriptives (eg. graph with survival rate per year) I want to use yearly data instead of quarterly to make it more compact (instead of 40 quarters, 10 years).
So ideally, I think it might be useful to have both quarterly and yearly information.

Thanks!

[Clyde Schechter]

by BvdIdNumber Years (Quarter), sort: egen new_FirmFailure3 = max(FirmFailure3)