Compute average of adjacent two quarters in panel data

[Rochelle Zhang]

Dear all,
I have a panel dataset for firm, year, and quarter observations. I need to compute the average of inventory from the current quarter and the prior quarter and use this average in my regression.

1. I do not know which function to use to extract quarter or if that is necessary

I know I need to use lag of inventory variable, but need help with creating a time variable used in lag.
Cyear is calendar year, invtq is quarterly inventory

Example generated by -dataex-. For more info, type help dataex
clear
input str6 firmid long datadate str6 datacqtr double invtq float(cyear quarter)
"002403" 18352 "2010Q1"     1284 2010 200
"002403" 18443 "2010Q2"     1265 2010 201
"002403" 18535 "2010Q3"     1369 2010 202
"002403" 18627 "2010Q4"     1204 2010 203
"002403" 18717 "2011Q1"     1322 2011 204
"002403" 18808 "2011Q2"     1492 2011 205
"002403" 18900 "2011Q3"     1357 2011 206
"002403" 18992 "2011Q4"     1384 2011 207
"002403" 19083 "2012Q1"     1463 2012 208
"002403" 19174 "2012Q2"     1521 2012 209
"002403" 19266 "2012Q3"     1697 2012 210
"002403" 19358 "2012Q4"     1657 2012 211
"005180" 18352 "2010Q1"  6312.82 2010 200
"005180" 18443 "2010Q2" 6083.429 2010 201
"005180" 18535 "2010Q3" 6408.808 2010 202
"005180" 18627 "2010Q4"  5905.91 2010 203
"005180" 18717 "2011Q1" 6475.368 2011 204
"005180" 18808 "2011Q2" 6862.216 2011 205
"005180" 18900 "2011Q3" 6304.284 2011 206
"005180" 18992 "2011Q4"  6017.48 2011 207
"005180" 19083 "2012Q1" 6406.789 2012 208
"005180" 19174 "2012Q2" 6210.087 2012 209
"005180" 19266 "2012Q3" 6510.875 2012 210
"005180" 19358 "2012Q4" 6454.388 2012 211

Thank you,
Rochelle

[Clyde Schechter]

You don't have to "extract" the quarter: it's already in your data set as the variable quarter. The first command in the code below verifies that the variable quarter does indeed correspond to the correct Stata internal format representation of the quarter shown as a string in variable datacqtr. After that, you also need to create a numeric version of the firmid variable. Based on what you show in the example data, I did this with -destring-.

//    VERIFY THAT VARIABLE quarter IS THE CORRECT NUMERIC REPRESENTATION OF
//    datacqtr AS A STATA QUARTERLY DATE VARIABLE
assert quarter == quarterly(datacqtr, "YQ")

//    CALCULATE MEAN OF EACH QUARTER'S invtq WITH THAT OF THE PRECEDING QUARTER
destring firmid, replace
format firmid %06.0f
xtset firmid quarter

But it is possible that in the full data set, some of the firmid's will turn out to contain non-numeric characters--in which case, the -destring- command will fail. If that happens, instead of the -destring- command, do this:

rename firmid str_firmid
encode str_firmid, gen(firmid)

[Rochelle Zhang]

Thanks a lot Clyde !

I forgot to mention, the quarter variable was generated by me using

gen quarter=qofd(datadate)

I did not know if datadate is daily date, but just tried it and did not know if my quarter is correct

I did inventory calculation below, it seems to be correct.

xtset firmid quarter
 
 gen L1_inv=l1.invtq
 
 gen Me_inv=(L1_inv+invtq)/2

[Clyde Schechter]

Yes. If you run -format datadate %td- and then look in the browser, you will see that datadate is in fact the Stata internal format representation of the last date of the quarter given in dataqtr. So if you ran -gen quarter = qofd(datadate)- then you indeed correctly "extracted" the quarter from datadate. Alternatively, you could have gotten it with -gen quarter = quarterly(datacqtr, "YQ")- and the results would have been the same because datacqtr shows, as a string, the same quarter.

Your calculation of Me_inv also appears correct. I had, in fact, developed similar code to post in #2: -gen wanted = (L1.invtq + invtq)/2-. I had intended to include it in the post, but I guess I made a copy/paste error and didn't include that last line. Sorry about that. But you did get it right on your own.

[Rochelle Zhang]

Thank you so much Clyde!

[Nick Cox]

As I understand it, back in 2014 you were registered as Rochelle Zhang and as Rstata. But now you've forgotten how to sign on as Rochelle Zhang. This is all easily soluble. Contact the Statalist administrators using the CONTACT US field on this site and ask for your identifier to be changed to Rochelle Zhang.