Creating a dummy variable which also applies to previous observations

[Jonas Lang]

Dear Statalist community,


I´m running an analysis of derivative use by mutual funds. I have an unbalanced panel data set with about 12.5 million observations. For each fund I have the different holdings per date. Each fund can be uniquely identified by id and date. To differ between derivative users and derivative non users I´d like to generate a dummy variable which equals 1, if the fund uses derivatives, and 0 otherwise.

I´ve created the dummy DerivativeUser. To illustrate I´ve prepared a short example with dataex:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long portfolioid int date float calmt str2 HoldingType str77 SecurityName float DerivativeUser
1 14700 482 "E" "Equity A"     0
1 14700 482 "E" "Equity B"     0
1 14883 485 "D" "Derivative A" 1
1 14883 485 "D" "Derivative B" 1
1 14883 485 "E" "Equity D"     0
2 14700 482 "E" "Equity A"     0
2 14700 482 "E" "Equity B"     0
2 14883 485 "E" "Equity A"     0
2 14883 485 "E" "Equity B"     0
3 14700 482 "E" "Equity D"     1
3 14700 482 "E" "Equity A"     0
3 14883 485 "E" "Equity B"     0
3 14883 485 "E" "Equity A"     0
3 14883 485 "E" "Equity B"     0
3 14975 488 "D" "Derivative A" 1
3 14975 488 "D" "Derivative B" 1
end
format %td date

You see fund no. 1 and 3 are for example derivative users and fund no. 2 is a non user. My problem is, that fund 1 is identified in the second quarter as user. Because of that I´d like to replace the previous 0 of the previous dates to a 1. If a fund uses only one derivative in the whole time period it´s a user fund and that should be transferred to all funds with this specific id. I´m not sure how to manage that stata assigns a 1, whether there is only one observation with a derivative holding.

I´m really new to Stata in terms of panel data, so I would be very pleased if you can help me with that.

Thank you very much in advance!

Best Regards

Jonas

[Carlo Lazzaro]

Jonas:
welcome to this forum.
Maybe you're looking for something along the following lines:

bysort portfolioid: egen New_DerivativeUser=max( DerivativeUser )

[Nick Cox]

In similar spirit, note that

 
 bysort portfolioid: egen New_DerivativeUser= total( DerivativeUser == 1 )

will count how the number of observations with values equal to 1 and then you can act on whether that is 1 for each identifier.

[Jonas Lang]

Hey Carlo, hey Nick!

Many thanks for the quick response and the very helpful solution! It works very well for my dataset.

It´s really nice to get such a instant help!

Kind Regards

Jonas