Expand a dataset using bysort

[Monica Muller]

I have a dataset that has data up to a certain year (2010). It's a panel data such that I have data of several firms from 2000 to 2010. I need to expand it and add 5 new rows after year 2010 (2011-2015) for each firm.
When I use expand 5 if year==2010, generate (original) all the new rows go to the end of dataset but I want to have 5 new rows for each firm. I tried bysort firmid : expand 5 if year==2010, generate (original) but I get the following error: expand may not be combined with by

What's my mistake?

Thanks

[Monica Muller]

Please let me know if you know how to do it. Thanks

[Mike Lacy]

My approach would be this: By hand, create five new blank observations (rows) in your data set. Enter 2011, ..., 2015 in the year variable for these rows. Then, use the -fillin- command (-fillin year firmid-) to create those blank observations for each firm.

[Robert Picard]

Here's another way

clear
set obs 10
gen firm = _n
expand 11
bysort firm: gen year = 1999 + _n
gen x = runiform()

// verify assumptions about the data
isid firm year, sort

tempfile f
save "`f'"

keep if year == 2010
keep firm
expand 5
bysort firm: gen year = 2010 + _n
append using "`f'"

isid firm year, sort

[Monica Muller]

Thank you very much. I am attaching an example of what i want to do in case my question was not clear. I want to add the yellow rows to the original data. Does your suggestion work for that? Thanks

[Robert Picard]

Did you try to run the example? It does exactly what you requested. Do not be confused with the initial part that created fake data. Just load you data in memory and run

tempfile f
save "`f'"

keep if year == 2010
keep firm
expand 5
bysort firm: gen year = 2010 + _n
append using "`f'"

isid firm year, sort

[Clyde Schechter]

Monica, please don't post Excel spreadsheet attachments to the forum. Some of the most active members of the Forum are not users of Microsoft Office products. Moreover, some of those who are will be reluctant to open such an attachment from a stranger, because Excel spreadsheets can contain active, and even malicious code. Everyone here uses Stata. So Stata file attachments will work. Or, it is usually more convenient to post code or short excerpts of data (-list- output) in code blocks. (See FAQ for how to create a code block.)

[Monica Muller]

Thank you very much Robert. It makes sense now