Reshaping and Transposing Data in Stata

[Vince Williams]

Dear Stata Users:
I have the following table in STATA. The problem is all the data is in one row. I am trying to create a quarterly panel data for which all the quarterly dates and amounts are in separate rows. How can I achieve this in Stata. Is there a transpose function?

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id str9 Company long(Period_End_Date Q4_Amount) byte(Q3_Amount Q2_Amount Q1_Amount) long(Q4_Filing_Date Q3_Filing_Date Q2_Filing_Date Q1_Filing_Date First_Date)
1360442 "MICROSOFT" 20088 -13070346 0 0 0 20193 20046 19954 19862 20193
end
format %tdD_m_Y Period_End_Date
format %tdD_m_Y Q4_Filing_Date
format %tdD_m_Y Q3_Filing_Date
format %tdD_m_Y Q2_Filing_Date
format %tdD_m_Y Q1_Filing_Date
format %tdD_m_Y First_Date

I am trying to reshape and transpose the data to look like this.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id str9 Company long Period_End_Date byte Quarter long(Amount Filing_Date First_Date)
1360442 "MICROSOFT" 20088 1         0 19862 20193
1360442 "MICROSOFT" 20088 2         0 19954 20193
1360442 "MICROSOFT" 20088 3         0 20046 20193
1360442 "MICROSOFT" 20088 4 -13070346 20193 20193
end
format %tdD_m_Y Period_End_Date
format %tdD_m_Y Filing_Date
format %tdD_m_Y First_Date

[Clyde Schechter]

On the assumption that variable id uniquely identifies observations in the original wide data:

reshape long Q@_Amount Q@_Filing_date, i(id) j(quarter)
rename Q_* *

does it.

If variable id does not uniquely identify observations in the wide data, then perhaps the combination of id and Company and Period_End_Date do, or something like that. Whatever the right combination of variables that identifies observations uniquely, that's what goes in the -iI()- option.

If there isn't any combination of variables that uniquely identifies observations in the wide data, then just create one with:

gen long obs_no = _n

and use obs_no in the -i()- option.