Repeated time values within panel r(451)

[Ercan Simsek]

I have a dataset with several observations for the same company for multiple years (where the company is identified by company_ID) and other variables for those companies, including a date variable. I want to perform regression, however the error is telling me that I have repeated time values in my panel. I have checked my data, and what I don't understand is:
1. My company_ID variable is in blue, I don't understand why this is the case and whether it is correct to use it in this way?
2. For each company_ID I have multiple date observations from 2003 up and until 2015. Is the error trying to explain to me that this is the problem?

I have checked several topics on this error but could not find a solution on how to deal with this. I hope that one of you can help me out with this.

[Carole J. Wilson]

Please read the FAQ (especially #12). Provide a sample of your data using dataex (described in the FAQ) and tell us exactly what you typed and what Stata gave you in return. Please use Code delimiters as described in the FAQ.

[Carlo Lazzaro]

Ercan:
I agree with Carole's remind about posting what you typed and wjhat Stata gave you back.
That said:
- for company_ID in blue, please see -help label-;
- its tipical of panel data to have multiple observations per ID. Did you take a look at -xt-? Did you -xtset- your data properly?

[Nick Cox]

Carole and Carlo are giving excellent advice. In particular, telling us that you want to perform regression is not informative here without knowing about your data and what your syntax is.

At one extreme, some kinds of regression require xtset data with distinct (panel identifier, time) combinations occurring at most once. The reported error could mean many things, including trying something that makes no sense and an incorrect xtset.

[Ercan Simsek]

Thanks for letting me know Carole J. Wilson. I have read the FAQ.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(company_ID date1)
1399 15795
1399 15886
1399 15978
1399 16070
1399 16161
1399 16252
1399 16344
1399 16436
1399 16526
1399 16617
1399 16709
1399 16801
1399 16891
1399 16982
1399 17074
1399 17166
1399 17256
1399 17347
1399 17439
1399 17531
1399 17622
1399 17713
1399 17805
1399 17897
1399 17987
1399 18078
1399 18170
1399 18262
1399 18352
1399 18443
1399 18535
1399 18627
1399 18717
1399 18808
1399 18900
1399 18992
1399 19083
1399 19174
1399 19266
1399 19358
1399 19448
1399 19539
1399 19631
1399 19723
1399 19813
1399 19904
1399 19996
1399 20088
1399 20178
1399 20178
1399 20269
1399 20361
1399 20453
end
format %td date1
label values company_ID company_ID
label def company_ID 1399 "285848107", modify

When I click on a company_ID observation (for example, 000360206), which is in blue, in the data editor, it shows a different value (in this case, 1) above the variables in the data editor where we can adjust the name of the variable and its value. Also I would like to mention that company_ID is a cusip observation. I have the cusip variable in my dataset as well (it is in red).

I used the following commands:

 
. sort company_ID date1

. tsset company_ID date1
repeated time values within panel
r(451);

To describe my data:

. describe

Contains data from /Users/ercansimsek/Desktop/Compustat.dta
  obs:        59,588                          
 vars:            55                          9 May 2016 14:14
 size:    22,703,028                          
-----------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------
gvkey           str6    %6s                   Global Company Key
date1           long    %td                   Data Date
fyearq          double  %6.0g                 Fiscal Year
fqtr            double  %4.0g                 Fiscal Quarter
indfmt          str12   %12s                  Industry Format
consol          str2    %2s                   Level of Consolidation -
                                                Company Interim Descriptor
popsrc          str2    %2s                   Population Source
datafmt         str12   %12s                  Data Format
cusip           str10   %10s                  CUSIP
conm            str58   %58s                  Company Name
curcdq          str4    %4s                   ISO Currency Code
datacqtr        str6    %6s                   Calendar Data Year and Quarter
datafqtr        str6    %6s                   Fiscal Data Year and Quarter
rdq             long    %d                    Report Date of Quarterly
                                                Earnings
atq             double  %18.0g                Assets - Total
ceqq            double  %18.0g                Common/Ordinary Equity - Total
cheq            double  %18.0g                Cash and Short-Term Investments
cshoq           double  %18.0g                Common Shares Outstanding
dlcq            double  %18.0g                Debt in Current Liabilities
dlttq           double  %18.0g                Long-Term Debt - Total
ibq             double  %18.0g                Income Before Extraordinary
                                                Items
mibtq           double  %18.0g                Noncontrolling Interests -
                                                Total - Balance Sheet
oibdpq          double  %18.0g                Operating Income Before
                                                Depreciation - Quarterly
pstkq           double  %18.0g                Preferred/Preference Stock
                                                (Capital) - Total
spiq            double  %18.0g                Special Items
EIDO            double  %18.0g                Extraordinary Items and
                                                Discontinued Operations
xsgaq           double  %18.0g                Selling, General and
                                                Administrative Expenses
cogsy           double  %18.0g                Cost of Goods Sold
doy             double  %18.0g                Discontinued Operations
oancfy          double  %18.0g                Operating Activities - Net Cash
                                                Flow
spiy            double  %18.0g                Special Items
xidocy          double  %18.0g                Extraordinary Items and
                                                Discontinued Operations
                                                (Statement of Cash Flows)
costat          str2    %2s                   Active/Inactive Status Marker
prccq           double  %18.0g                Price Close - Quarter
sic             str4    %4s                   Standard Industry
                                                Classification Code
SPI             float   %9.0g                 
sales           double  %18.0g                Sales/Turnover (Net)
CE              float   %9.0g                 
date2           float   %9.0g                 
date3           float   %9.0g                 
non_numeric     byte    %8.0g                 
company_ID      long    %9.0g      company_ID
                                              CUSIP
lsales          float   %9.0g                 
changesales     float   %9.0g                 
negchangesales  float   %9.0g                 
cashfromopera~s float   %9.0g                 
accruals        float   %9.0g                 
operatingassets float   %9.0g                 
operatingliab~s float   %9.0g                 
NOA             float   %9.0g                 
lNOA            float   %9.0g                 
averageNOA      float   %9.0g                 
ATO             float   %9.0g                 
laccruals       float   %9.0g                 
lCE             float   %9.0g                 
-----------------------------------------------------------------------------
Sorted by: company_ID  date1
     Note: Dataset has changed since last saved.

[Ercan Simsek]

Thanks for the comments Carlo and Nick. I will have a look at -help label- and -xtset- to find more information on the error.

[Carole J. Wilson]

Indeed, your Company ids are labeled. To see the labels associated with values of company_ID

lab list company_ID

Or you can

browse, nolabel

Your tsset command seems fine, it is just that you have multiple observations with the same dates within a company. In the example you gave:

tab date1

You'll see you have two observations for 31mar2015.

You can flag those cases where there are multiple observations with the same date:

bysort company_ID (date1): gen flag=1 if date1==date1[_n-1] | date1==date1[_n+1] 
list if flag==1
*or
browse if flag==1

Now you can inspect these cases and see the cause of these duplicate dates.

[Nick Cox]

Thanks for the example. Even the example shows what would be a problem for tsset

. duplicates list company_ID date1

  +-------------------------+
  | obs:   compan~D   date1 |
  |-------------------------|
  |   49       1399   20178 |
  |   50       1399   20178 |
  +-------------------------+

That's equivalent to, but simpler than, Carole's syntax.

Whether that's a data error or something expectable I can't say.

FYI, I have no idea what CUSIP data are personally, although that should make sense to others here.

You have yet to say exactly what kind of regression you are planning.

[Carlo Lazzaro]

Ercan:
the following id causes all your troubles:

-> date1 = 31mar2015

 company_ID |      Freq.     Percent        Cum.
------------+-----------------------------------
  285848107 |          2      100.00      100.00
------------+-----------------------------------
      Total |          2      100.00

PS: Cyber-crossed with Nick's reply.

[Ercan Simsek]

Thank you Nick and Carole for your feedback that helps me to proceed to the solution. I want to perform the following regression:

 reg CE lCE ATO accruals laccruals changesales negchangesales

I don't know exactly how to deal with this. I guess that deleting the duplicates would solve the problem since the multiple observations with the same date also have the same values for the other variables in my dataset, indicating that I can delete them. Would this be a correct approach?

[Carlo Lazzaro]

Ercan:
if you have panel data, you should go -xtreg- first; pooled -regression- can be OK in some instances only.
More substantively, I would flag the troubled observations instead of deleting them (just in case...) and rule them out from the regression via a conditional -if-.

[Nick Cox]

Following Carlo's good advice,

help duplicates

gives explanation of various tools.

[Carole J. Wilson]

Your regression command does not take into account the cross-sectional time-series nature of the data. You issue the xtset command, but this is not taken into account unless you use specific commands (usually with the xt prefix).

[Ercan Simsek]

I don't understand how flagging helps me to tell Stata that I have a panel data and that I want to perform regressions?

I want to try out dropping the duplicates in my dataset as well as flagging multiple observations, however it is not clear to me what command to use to do this in Stata (I am a beginner). Do I have to use the following commands:

1. For dropping duplicates if flag==1

drop if flag==1

2. Flagging multiple observations and excluding them from the regression

 
 bysort company_ID (date1): gen flag=1 if date1==date1[_n-1] | date1==date1[_n+1]   
 reg CE lCE ATO accruals laccruals changesales negchangesales if flag==.

[Carlo Lazzaro]

Ercan:
flagging will simply tells Stata that you want rule the flagged observations out of the estimate whichever; hence, it does not qualify which kind of statistical procedure you're going to run.
My previus reply was along the lines of your code #2.