Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming a firm identifier <code>firmid</code>, this is another way to think about the problem, but with a different data structure. Make sure you have a saved copy of your dataset before you do this.</p> <pre><code>expand 2 bysort firmid : gen eitherdate = cond(_n == 1, start_dt, end_dt) by firmid : gen score = cond(_n == 1, 1, -1) sort eitherdate gen living = sum(score) by eitherdate : replace living = living[_N] </code></pre> <p>So, </p> <ol> <li><p>We <code>expand</code> each observation to 2 and put both dates in a new variable, the start date in one observation and the end date in the other observation. </p></li> <li><p>We assign a score that is 1 when a firm starts and -1 when it ends. </p></li> <li><p>The number of firms is increased by 1 every time a firm starts and decreased by 1 every time one ends. We just need to sort by date and the number of firms is the cumulative sum of those scores. (EDIT: There is a fix for changes on the same date.) </p></li> </ol> <p>This new data structure could be useful for other purposes. </p> <p>There is a write-up at <a href="http://www.stata-journal.com/article.html?article=dm0068" rel="nofollow">http://www.stata-journal.com/article.html?article=dm0068</a> </p> <p>EDIT: </p> <p>Notes in response to @Roberto Ferrer (and anyone else who read this): </p> <ol> <li><p>I fixed a bad bug, which made this too difficult to understand. Sorry about that. </p></li> <li><p>The dates used here are just the dates at which firms start and end. There is no evident point in evaluating the number of firms at any other date as it would just be the same number as the previous date used. If you needed, however, to interpolate to a grid of dates, copying the previous count would be sufficient. </p></li> <li><p>It is important not to confuse the Stata function <code>sum()</code> which returns the cumulative sum with any <code>egen</code> function. The impression that <code>egen</code>'s <code>total()</code> is an alternative here was a side-effect of my bug. </p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POCount number of living firms efficiently
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNick Cox
    1. USNick Cox
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI haven't been able to read the article but let me see if I understand: Your final result establishes the number of living firms not for all possible dates (like in the loop), but for each date in variable `eitherdate`. If the number is negative, then those are dying firms. Is that correct? Why not use `egen, total()` in substitution for the last two lines? Is it because of speed issues?
      singulars
      1. PO
      1. USRoberto Ferrer
    2. COI have added some notes in the answer.
      singulars
      1. PO
      1. USNick Cox
    3. COQuite an elegant solution; very ingenious.
      singulars
      1. PO
      1. USRoberto Ferrer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload