Unbalanced panel data

[b_lobo]

I am trying to create a balanced panel dataset. Attached is a sample XLS file with 4 cross-sections or firms. The total time periods should be 191 months from 1998:6 through 2014:4. Thus I need a balanced panel of 4 x 191 = 764 obs.

The data are stacked by firm and each firm has is indexed by "Firm_no". Here is what I am doing:

CALENDAR(M) 1998:6
ALL 2014:4
open data sample.xls
data(format=xls,org=obs) / event date Firm_No Estimate Actual

*** TRANSFORMING TO A BALANCED PANEL ***********
* this is an unbalanced panel with 4 blocks (cross sections/firms)
* I need 191 obs for each bloc including missing obs

PFORM(indiv=event) PESTIMATE
# ESTIMATE
PFORM(indiv=event) PACTUAL
# ACTUAL

**** CHANGE TO PANEL DATA CALENDAR *****
CAL(PANELOBS=191) 1998:6
ALL 4//191
COPY(format=rats) / pactual pestimate

This doesn't seem to be doing it for me. What am I doing incorrectly?

[TomDoan]

Perhaps I'm not understanding what you're trying to do, but it seems to me that firm_no should be representing individuals, rather than event. Plus, you need to use both the INDIV and TIME options on PFORM to get the observations to line up. Also, the original data aren't monthly---it's a stack of four time series with gaps in them. Also, even across all the firms you don't have 191 time series observations. The following will block what you actually have into a 4 x 163 panel.


open data sample.xls
data(format=xls,org=obs) / event Firm_No Estimate Actual

*** TRANSFORMING TO A BALANCED PANEL ***********
* this is an unbalanced panel with 4 blocks (cross sections/firms)
* I need 191 obs for each bloc including missing obs

PFORM(indiv=firm_no,time=event) PESTIMATE
# ESTIMATE
PFORM(indiv=firm_no,time=event) PACTUAL
# ACTUAL

**** CHANGE TO PANEL DATA CALENDAR *****
CAL(PANELOBS=%nobs)
ALL %ngroup//%nobs
COPY(format=rats) / pactual pestimate

[b_lobo]

Thank you. Doing this generates 163 obs for firms 1, 2 and 3 but only 56 obs for firm 4. Also the start event for each firm is different. I'm trying to get all the firms to start with event #1 and end with event #191 i.e. a total of 4 x 191 = 764 rows of data including missing obs. Any thoughts?

[TomDoan]

Thank you. Doing this generates 163 obs for firms 1, 2 and 3 but only 56 obs for firm 4.

Put a hard range on the COPY instruction as 1//1 4//163. The default range will truncate the last firm at its last data point.

Also the start event for each firm is different. I'm trying to get all the firms to start with event #1 and end with event #191 i.e. a total of 4 x 191 = 764 rows of data including missing obs. Any thoughts?

Your data set has no event number less than 26. What are 1-25 supposed to be?

[b_lobo]

This is a small sample from a bigger file. There are other cross sections that start at event #1. For firms that don't have obs for event #1, I'd like the missing obs code to show. No firm in the entire file has all 191 obs, so I expect that all the cross sections will have some missing obs. I'm setting up the file in case I need to run random effects or other variations with this dataset.

[TomDoan]

PFORM doesn't invent identifiers that don't exist. When you use a bigger file that has event numbers from 1 to 191, you'll get 191 per individual.