GVKEY<\/code>–DATADATE<\/code> can be verified using the following Stata command:<\/p>\n`duplicates report gvkey datadate if indfmt==”INDL” & datafmt==”STD” & popsrc==”D” & consol==”C”`<\/p>\n
This command should return “no duplicates”.<\/p>\n
The quarterly data (FUNDQ) is a bit more complicated. First of all, applying the same conditions won’t work. In fact, 99.7% observations in FUNDQ already satisfy these conditions. However, duplicate GVKEY<\/code>–DATADATE<\/code>s still exist in FUNDQ. The root cause of these duplicates is a firm changing its fiscal year-end. I use the following example for illustration:<\/p>\n![\"\"](\"https:\/\/www.kaichen.work\/wp-content\/uploads\/2024\/06\/Screen-Shot-2024-06-20-at-16.32.47-PM.png\")
<\/a><\/p>\nVariable definition: FYEARQ<\/code> – fiscal year; FQTR<\/code> – fiscal quarter; FYR<\/code> – fiscal year-end month; DATACQTR<\/code> – calendar quarter; DATAFQTR<\/code> – fiscal quarter; ATQ<\/code> – total assets; NIQ<\/code> – quarterly net income; NIY<\/code> – year-to-date net income.<\/p>\nIn this example, duplicates exist for three DATADATE<\/code>s: 2010-03-31, 2010-06-30, and 2010-09-30. The data suggest that on March 31, 2010, the firm changed its fiscal year-end from March 31 to December 31 (i.e., FYR changed from 3 to 12). As a result, 2010-03-31 appeared twice in FUNDQ, once as fiscal 2009Q4 (based on the old fiscal year-end) and once as 2010Q1 (based on the new fiscal year-end). FUNDQ also reports additional duplicates for the subsequent two quarters (I don’t know why). Additionally, if we compare NIQ and NIY as highlighted in the red rectangle, the observation for fiscal 2009Q4 indicates NIY > NIQ, which makes sense as NIY is a four-quarter sum and NIQ is single-quarter net income. In contrast, the observation for fiscal 2010Q1 indicates NIQ = NIY as both are single-quarter net income in this case.<\/p>\nSo, what’s the best strategy to remove duplicate GVKEY<\/code>–DATADATE<\/code>s?<\/p>\nBefore we answer this question, let’s take a closer look at duplicate GVKEY<\/code>–DATADATE<\/code>s in FUNDQ, which reveals that 99.8% of GVKEY<\/code>–DATADATE<\/code>s in FUNDQ are unique as of December 5, 2107. This suggests that no matter how we deal with duplicates, even simply delete all of them, our results probably won’t change in a noticeable way.<\/p>\nThat said, if we want to remove duplicates more carefully, COMPUSTAT provides the following clue:<\/p>\n
In the definition of DATAFQTR<\/code>, COMPUSTAT notes that,<\/p>\nNote: Companies that undergo a fiscal-year change may have multiple records with the same datadate. Compustat delivers those multiple records with the same datadate but each record relates to a different fiscal year-end period.<\/p>\n
Rule: Select records from the co_idesind data group where datafqtr is not null, to view as fiscal data.<\/p><\/blockquote>\n
Unfortunately, I find that the suggested rule is not the best strategy because COMPUSTAT seems to set DATAFQTR<\/code> as missing or non-missing inconsistently. In my opinion, the best strategy is to retain the GVKEY<\/code>–DATADATE<\/code> that reflects the most recent change of fiscal year-end<\/strong>. This means, in the above example, we should delete the following observations:<\/p>\n\n- \n
\nDATADATE<\/code> = 2010-03-31 and FYR<\/code> = 3<\/li>\nDATADATE<\/code> = 2010-06-30 and FYR<\/code> = 3<\/li>\nDATADATE<\/code> = 2010-09-30 and FYR<\/code> = 3<\/li>\n<\/ul>\n<\/li>\n<\/ul>\nSuppose we have converted FUNDQ to Stata format. The following Stata code will implement the above strategy. The code will also fill in missing DATAFQTR<\/code> and remove duplicate GVKEY<\/code>–DATAFQTR<\/code>, which will later allow us to use the tsset<\/code> command and perform lag and change calculations in Stata, e.g., to get beginning-of-quarter total assets or calculate quarterly changes in sales. Stata really shines in lag and change calculations for panel data\u2014a superb advantage over SAS.<\/p>\nuse fundq, clear\r\n\r\n\/\/ Keep necessary variables only for testing\r\nkeep gvkey datadate fyearq fqtr fyr datacqtr datafqtr atq saleq\r\n\r\n\/\/ Generate unique ID for stable sorting (keep original order when tied)\r\ngen _id = _n\r\n\r\n\/\/ Check if gvkey, datadate, and fyr are missing - NO\r\nassert !missing(gvkey, datadate, fyr) \/\/ manually clean up missing values\r\n\r\n\/\/ Convert datacqtr and datafqtr to datatime\r\ngen _datacqtr = quarterly(datacqtr, \"YQ\")\r\ngen _datafqtr = quarterly(datafqtr, \"YQ\")\r\nformat _datacqtr _datafqtr %tq\r\ndrop datacqtr datafqtr\r\nrename _datacqtr datacqtr\r\nrename _datafqtr datafqtr\r\norder datacqtr datafqtr, after(fyr)\r\n\r\n\/\/ Check if datafqtr is a simple combination of fyearq and fqtr - YES\r\ngen _datafqtr = yq(fyearq, fqtr)\r\ngen _diff = _datafqtr - datafqtr\r\nsum _diff, detail\r\ndrop _datafqtr _diff\r\n\r\n\/\/ Check if datacqtr is simply based on datadate - NO. Jan, Apr, Jul and Oct will be coded as the previous quarter\r\ngen _datacqtr = qofd(datadate)\r\nformat _datacqtr %tq\r\ngen _diff = datacqtr - _datacqtr\r\nsum _diff, detail\r\ndrop _datacqtr _diff\r\n\r\n\/\/ Remove duplicate gvkey-datadate\r\nduplicates tag gvkey datadate, ge(dup)\r\ntab dup\r\nassert dup == 1 | dup == 0 \/\/ if false, manually clean up dup >= 2\r\n\r\nsort gvkey datadate _id\r\n\r\nby gvkey: egen is_fyr_changed = max(dup)\r\ntab is_fyr_changed\r\n\r\nby gvkey: egen max_dup_gvkey_datadate = total(dup)\r\nsum max_dup_gvkey_datadate, detail\r\nlocal iter = r(max) - 2\r\n\r\ngen last=1 if dup==1 & dup[_n+1]==0\r\nreplace last=0 if dup==1 & last==.\r\n \r\nby gvkey: gen newfyr=fyr[_n+1] if last==1\r\nby gvkey: replace newfyr=newfyr[_n+1] if last==0 & last[_n+1]==1\r\nforvalues i = 1\/`iter' {\r\n\tby gvkey: replace newfyr=newfyr[_n+1] if last==0 & last[_n+1]==0\r\n}\r\n\r\ndrop if dup==1 & fyr!=newfyr\r\n\r\nduplicates report gvkey datadate \/\/ no duplicates\r\n\r\ndrop dup is_fyr_changed max_dup_gvkey_datadate last newfyr\r\n\r\nsave fundq_no_dup_gvkey_datadate, replace\r\n\r\n\/\/ Fill in missing datafqtr\r\n* generate next fiscal year end date\r\ngen _fyearend_1 = lastdayofmonth(mdy(fyr, 1, year(datadate)))\r\ngen _fyearend_2 = lastdayofmonth(mdy(fyr, 1, year(datadate)+1))\r\n\r\ngen fyearend = _fyearend_1 if datadate <= _fyearend_1\r\nreplace fyearend = _fyearend_2 if datadate > _fyearend_1\r\nformat fyearend %td\r\norder fyearend, after(datafqtr)\r\n\r\n* generate _datafqtr based on Compustat Manual\r\ngen _month_diff = mofd(fyearend) - mofd(datadate)\r\n\r\ngen _fyearq = year(fyearend)\r\nreplace _fyearq = year(fyearend) - 1 if month(fyearend) <= 5\r\n\r\ngen _datafqtr = yq(_fyearq, 1) if _month_diff == 9\r\nreplace _datafqtr = yq(_fyearq, 2) if _month_diff == 6\r\nreplace _datafqtr = yq(_fyearq, 3) if _month_diff == 3\r\nreplace _datafqtr = yq(_fyearq, 4) if _month_diff == 0\r\nformat _datafqtr %tq\r\n\r\n* check if datafqtr is correct if datafqtr is not missing - YES\r\ngen _diff = datafqtr - _datafqtr if datafqtr!=.\r\nsum _diff, detail\r\ndrop _diff\r\n\r\n* replace missing datafqtr with computed _datafqtr\r\nreplace datafqtr = _datafqtr if datafqtr == .\r\n\r\n\/\/ Remove duplicate gvkey-datafqtr\r\nduplicates tag gvkey datafqtr, ge(dup)\r\ntab dup\r\n\r\nby gvkey: egen has_dup=max(dup)\r\ntab has_dup\r\n\r\ngsort - _id\r\nduplicates drop gvkey datafqtr, force\r\nsort _id\r\n\r\nduplicates report gvkey datafqtr \/\/ no duplicates\r\ndrop dup has_dup _*\r\n\r\nreplace fqtr = quarter(dofq(datafqtr)) if fqtr==.\r\nassert !missing(gvkey, datadate, fyr, datafqtr, fqtr) \/\/ manually investigate missing values\r\n\r\nsave fundq_no_dup_gvkey_datafqtr, replace<\/pre>\nPlease note: I also agree with one of the readers’ comments that “(how to remove duplicates) depends on what you need”. For example, in one of my projects, I want to examine three-day CAR around earnings announcement date (RDQ<\/code>) and use total assets as the deflator in my regression. As a result, when duplicate GVKEY<\/code>–DATADATE<\/code>s exist, the one with non-missing RDQ<\/code> and ATQ<\/code> will be preferred if I want to retain as many observations as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"The annual data (FUNDA) is easy to deal with; we just need to apply the following conditions: `indfmt==”INDL” & datafmt==”STD” & popsrc==”D” & consol==”C”` If we have converted FUNDA to Stata format, the uniqueness of GVKEY–DATADATE can be verified using … Continue reading