Generating New Variable in an Existing Panel

[Davia Downey]

I have a panel data set which has some data recorded annually and some that are recorded quarterly. I want to transform the quarterly (counts of new jobs created in a county) into a new variable which has the average new jobs created annually. I am having trouble getting STATA to average the existing new jobs variable and creating one annual new job variable for each case. It keep doing it by all the quarters and disregards the fact these are case (county) specific. Is this a two step process or is there one quick way to do this in one line of syntax?

Note: the dataset has years from 1998-2015 and quarterly new jobs data for all years. The number of cases (i.e., the unit in the panel) is 37 counties.

Many thanks in advance!

[Clyde Schechter]

Had you posted an example of your data and shown the code you tried, you could have gotten more specific advice. I'll make some guesses about your data set. I assume that each observation has a variable showing the calendar quarter, let's call it qdate. I assume this variable is a Stata internal format numerical date. You may also have a variable with the calendar year, let's call it year. And you also have a variable called county which identifies the county described by the observation.

Now, I gather the variable you want to annualize is new jobs created. If that is the case, I don't understand why you want to average the quarterly results. I would think you would want to add them, and that is what the code below does. Let's call this quarterly variable new_jobs_created.

by county year, sort: egen annual_new_jobs_created = total(new_jobs_created

If your data set is not as I've described it (other than different names for the variables, which obviously can be substituted in the code) then post back showing what the data actually do look like. If you end up doing that, do not fail to use the -dataex- command to post the example data. Run -ssc install dataex- to install it into your Stata setup. Then run -help dataex- and read the simple instructions for using it.

[Davia Downey]

It was the egen part of this line of code I had incorrect. Thanks for the assistance.