Reshaping quarterly data from wide to long

[Noah Spencer]

I have a dataset of state-level quarterly GDP data that looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 state float(rgdpq1_2005 rgdpq2_2005 rgdpq3_2005 rgdpq4_2005 rgdpq1_2006 rgdpq2_2006 rgdpq3_2006 rgdpq4_2006 rgdpq1_2007 rgdpq2_2007 rgdpq3_2007 rgdpq4_2007 rgdpq1_2008)
"Alabama"               182600.4  184625.7  184134.9  186116.9    186539  187489.6  187442.8  187611.6  187376.1  188746.2  189934.1  189953.7  188933.9
"Alaska"                 45176.1   45776.9   45501.7   46173.5   47346.6   48911.2   49486.7   51016.2   51384.8   52275.8   52106.7   51116.5   49885.8
"Arizona"               256521.4  260884.6  265619.7  266278.2  271247.2  271897.9  275090.9  278481.7    279269  282276.6  285827.2  285630.2  285356.3
"Arkansas"              104693.3  105271.5  105872.6  108082.4  108122.4  109421.7  108515.5  107782.7  105496.6  106460.1  106792.1  111675.6  106514.9
"California"           1896451.4   1913819 1940272.3 1956825.6 1998965.4   1992185 2003116.6 2015482.8 2010197.5 2028647.3 2047151.8 2078772.3   2048458
"Colorado"              250734.2  251939.1  253059.7  250500.7  256879.2  255511.4  259127.6  258202.5  261339.4  265303.1    267714  273193.4  270427.7
"Connecticut"             236627  238189.5  237983.3  241398.4  249100.4  246858.5  246937.1  250039.3  258116.3  258100.6  258695.3  256899.9  269900.2
"Delaware"               58721.4   57325.3   57194.8   64098.7   60658.4   61328.7   60745.3   59764.7   60383.9   61806.6   60284.7   59436.3   58734.6
"District of Columbia"     99279   99650.1   99480.5   99513.3   99556.9   99452.7  100836.2   99376.3  100978.7    101307  104347.3  104321.6  106420.7
"Florida"                 804997  810485.1  824830.4  822710.5  834561.2  837003.8    839043    849247  844299.4  849761.1  855755.6  850378.4  835076.3
"Georgia"               442968.3  445971.4  447131.5  452540.3  450526.7    451071  453474.6  457056.1  450037.1  454087.7  458100.8  461390.3  450430.3
"Hawaii"                 66097.3   66774.7     67374   68249.4   68079.2   68669.1   69149.5   69139.5   69085.4   69427.6   70549.8   71258.7   71000.7
"Idaho"                    54984   55851.9   56747.8   56588.4   57675.9   58189.5   57011.7   57577.9   57953.6   58222.4   58687.5   59820.1   59894.2
"Illinois"              685519.3  685960.8  689684.2    689662  703368.2  704060.6  703568.9  708312.6    707704  710808.8  712608.8  719357.6  708830.7
"Indiana"               287211.6  290265.2    291820  292015.9  294718.5  295480.8  296252.6    297999  298025.7  303170.3  305942.1  309204.7  307348.6
"Iowa"                  144118.8  144576.1  145571.2    144043  145802.3  146800.9  145883.4  147734.8  150013.6  153268.3  153783.2  154382.1    153042
"Kansas"                  124051  124245.1  124798.9  122704.1  129547.7  130473.2  129955.9  131481.3  135248.5  136847.2  137355.1  138923.6  140367.8
"Kentucky"              168139.2  170967.1  172424.6  173190.4  175521.3  176473.8  175692.2  174943.3  172852.1  173961.1  174241.5  176574.4  176193.1
"Louisiana"             247839.3  250917.6  246429.1  245906.9  246621.5  246043.3  244566.2  244770.1  233339.3  230236.5  229263.2  228631.8  229226.5
"Maine"                  54479.3   54801.7   54956.6     55470   55405.9   55684.8   55324.8   55378.1   54892.2     55510   55586.9   55450.5   55298.8
"Maryland"              296104.9  299499.2  304913.3  306125.2  304842.7  305007.2    306216  307110.4  303858.1  306963.1  306385.5  309816.6  313558.2
"Massachusetts"         383214.4  384392.9  389005.8  390600.7    393070  395014.2  393542.7  400516.5    399634  403314.8  408509.3  414226.5  416127.1
"Michigan"              457543.9  458905.7  455627.9  456473.4  451470.7  452234.2    449695  446014.1  449892.4  449090.6  448578.8  440210.8  432787.5
"Minnesota"             281852.6    283117  288035.3  285542.9  285415.2  287334.2  282496.4    282282    282063  286921.1  286994.2  289218.5    289988
"Mississippi"            95489.6   95713.4   96370.4   99709.2     97659   99204.4   99260.5   99899.4   99369.3     99175  100794.7    101508  101668.2
"Missouri"              263559.7  265589.6    266296  267495.1  267349.1    269138    266414  265983.3  262968.8  267235.4  269020.9  271539.5  271435.9
"Montana"                  37305   37141.6   37281.1     37768   37951.3   38388.5   38843.5   39126.9   40046.9   40484.9   40459.5   40761.9   40844.6
"Nebraska"                 87583   88616.5   89764.4   91272.4   90564.2   93668.4     91423   91675.3   93596.2     93600     93420   95027.2   94502.9
"Nevada"                134532.2  138422.9  137939.9  139140.2  141661.2  143630.9  142844.5  143731.6  143240.3  143140.2  143931.9  146115.9  142860.7
"New Hampshire"          64678.5   65058.8   65251.9   65148.2   65820.3   65796.2   66388.8   68507.5   66183.4   67681.2   66653.9     66896   66947.5
"New Jersey"            502553.8    506024  511267.4  511733.6  513554.5  519363.7  515626.8  518180.5  512404.9    519973  521115.2  530100.3    539518
"New Mexico"             84403.3   84572.2   83546.3   83615.9   85828.6   85960.5   86041.9   86402.7   86206.9     86846   87363.4   86538.6   85719.9
"New York"             1152076.8 1162884.8   1173856 1186746.8 1204862.9 1196722.4 1190282.6 1205239.8 1242197.9 1203375.8 1198486.5 1190198.5 1204105.5
"North Carolina"        405614.5  406017.2  410065.7  414318.2    431596  434108.1  433750.2  437382.5  440263.7  437636.7  436759.7  441180.3  451921.7
"North Dakota"           29298.3   29377.3   29250.8   29454.5   30090.9   30746.6   30605.9   30917.1     31400     31759     32108     32822   33781.9
"Ohio"                  540875.9  544850.9  548967.3  549180.8  545872.8  546946.3    545511  543686.6  546321.3  546289.6    547574  546732.4  546235.3
"Oklahoma"              143227.5  144419.3  143920.5  142317.3  158117.7    152160  150636.6  150516.5  152798.9    155499  158241.1  158339.1  158301.8
"Oregon"                156860.6  157099.8  158541.6  160151.8  164997.2  165009.7  166508.8  167913.1  165278.9  167422.5  170354.5  172844.7  173488.5
"Pennsylvania"          588074.8  592847.9  598342.4  600155.3  591567.8  595622.2  599524.9  602162.1  605914.8  611075.4  621664.1  630585.2  629858.6
"Rhode Island"           50513.3   51300.2   51431.3   52233.9   51900.4     53154   53709.1   52834.7   52444.6   51521.5   51239.7   50670.4   50997.4
"South Carolina"          168167    169187  169777.8  170822.2  172902.3  173113.4  173024.6  174443.3  174930.5  177782.7  179947.7  181237.5  178486.2
"South Dakota"           35847.9   35651.3   35865.2   36004.5   36296.9     36556   36410.9   36139.5   37813.6   37968.4   37503.8   37737.9   39922.4
"Tennessee"             265145.2  266672.9  270176.9  271571.7  273436.5  275810.2  274295.9  275974.2  269551.8  271695.2  273751.4  277897.7  276748.3
"Texas"                1139658.4 1135433.3 1137918.6 1152484.8   1201785 1221094.9 1234618.8 1256690.6 1264535.4 1289259.4 1292203.3 1298772.8 1280086.4
"Utah"                  107245.4    108775  110924.6  113240.8  115939.5  117552.1  119222.9  121214.9  121062.3    124186  124527.8  125565.3  123900.2
"Vermont"                26990.1   26867.8   26986.5   27137.9   27388.1   27220.2     27110   26796.9   26609.9   26805.9   27029.9     27101   27833.2
"Virginia"              405352.9  410991.4  415922.1  416072.1  420969.4  420095.8  419481.9  418935.4  420943.2  422438.1  427118.7  429258.2  428421.1
"Washington"            336337.3  340084.4  339120.4  346169.7  350512.6  351494.8  353237.7  357808.9  360083.4  370587.8  377662.9  384388.3  379560.8
"West Virginia"          65341.8   66167.6   65491.4   65712.8   66583.1   66945.9   66904.8   67011.9   66603.2   66999.8   67443.6   67429.7   67405.9
"Wisconsin"               261144  263150.4    266338    264162  267734.4  268156.5    268477  268617.8  268832.6  269463.8  270768.7  270669.4    268392
"Wyoming"                33407.1   34028.5   33065.6   33434.8   35812.4   37271.1   38626.6   39543.7   40073.4   40549.5   40652.2   41253.9   41601.8
end

I am trying to reshape it from a wide to long format using the command

reshape long rdgp, i(state) j(quarter) string

.

However, I get the error message

no xij variables found
You typed something like reshape wide a b, i(i) j(j).
reshape looked for existing variables named a# and b# but could not find any.

What is the issue with my code?

[Øyvind Snilsberg]

reshape long rgdpq, i(state) j(quarter) string

[Noah Spencer]

Thanks!

[Nick Cox]

You need to follow with something like

gen qdate = quarterly(quarter, "QY") 
format qdate %tq

and likely

encode state, gen(stateid) 
xtset stateid qdate