Interchanger Observations and Variable Name. Possible to Switch Observations to Varname?

[Ali Sheikhpour]

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 RegionName str16(B C) str17(D E F) str16(G H I J K L)
"City" "San Francisco" "Pittsburg" "Alameda" "San Francisco" "Fremont" "Hayward" "Antioch" "Fremont" "Hayward" "Union City" "Brentwood"
"State" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA"
"CountyName" "San Francisco" "Contra Costa" "Alameda" "San Francisco" "Alameda" "Alameda" "Contra Costa" "Alameda" "Alameda" "Alameda" "Contra Costa"
"2010-01" "" "" "" "" "" "" "" "" "" "" ""
"2010-02" "" "" "" "" "" "" "" "" "" "" ""
"2010-03" "" "" "" "" "" "" "" "" "" "" ""
"2010-04" "" "" "" "" "" "" "" "" "" "" ""
"2010-05" "" "" "" "" "" "" "" "" "" "" ""
"2010-06" "" "" "" "" "" "" "" "" "" "" ""
"2010-07" "" "" "" "" "" "" "" "" "" "" ""
"2010-08" "" "" "" "" "" "" "" "" "" "" ""
"2010-09" "" "" "" "" "" "" "" "" "" "" ""
"2010-10" "" "" "" "" "" "" "" "" "" "" ""
"2010-11" "" "" "" "" "" "" "" "" "" "" ""
"2010-12" "" "" "" "" "" "" "" "" "" "" ""
"2011-01" "" "" "" "" "" "" "" "" "" "" ""
"2011-02" "" "" "" "" "" "" "" "" "" "" ""
"2011-03" "" "" "" "" "" "" "" "" "" "" ""
"2011-04" "" "" "" "" "" "" "" "" "" "" ""
"2011-05" "" "" "" "" "" "" "" "" "" "" ""
"2011-06" "" "" "" "" "" "" "" "" "" "" ""
"2011-07" "" "" "" "" "" "" "" "" "" "" ""
"2011-08" "" "" "" "" "" "" "" "" "" "" ""
"2011-09" "" "" "" "" "" "" "" "" "" "" ""
"2011-10" "" "" "" "" "" "" "" "" "" "" ""
"2011-11" "" "" "" "" "" "" "" "" "" "" ""
"2011-12" "" "" "" "" "" "" "" "" "" "" ""
"2012-01" "" "" "" "" "" "" "" "" "" "" ""
"2012-02" "" "" "" "" "" "" "" "" "" "" ""
"2012-03" "" "" "" "" "" "" "" "" "" "" ""
"2012-04" "" "" "" "" "" "" "" "" "" "" ""
"2012-05" "" "" "" "" "" "" "" "" "" "" ""
"2012-06" "" "" "" "" "" "" "" "" "" "" ""
"2012-07" "" "" "" "" "" "" "" "" "" "" ""
"2012-08" "" "" "" "" "" "" "" "" "" "" ""
"2012-09" "" "" "" "" "" "" "" "" "" "" ""
"2012-10" "" "" "" "" "" "" "" "" "" "" ""
"2012-11" "" "" "" "" "" "" "" "" "" "" ""
"2012-12" "" "" "" "" "" "" "" "" "" "" ""
"2013-01" "" "" "" "" "" "" "" "" "" "" ""
"2013-02" "" "" "" "" "" "" "" "" "" "" ""
"2013-03" "" "" "" "" "" "" "" "" "" "" ""
"2013-04" "" "" "" "" "" "" "" "" "" "" ""
"2013-05" "" "" "" "" "" "" "" "" "" "" ""
"2013-06" "" "" "" "" "" "" "" "" "" "" ""
"2013-07" "" "" "" "" "" "" "" "" "" "" ""
"2013-08" "" "" "" "" "371.6906292702" "" "" "" "" "" ""
"2013-09" "" "" "" "" "373.953695458593" "" "" "" "" "" ""
"2013-10" "" "" "386.804657179819" "730.366492146597" "378.995578016425" "" "" "" "" "" ""
"2013-11" "" "" "383.534074276216" "699.85" "378.872145332032" "" "" "386.597257209507" "263.189448441247" "310.735346358792" "189.298334174659"
"2013-12" "" "176.784523015344" "386.029411764706" "714.285714285714" "377.06228956229" "" "164.961496149615" "390.354454810399" "263.617196130736" "322.060353798127" "191.140278917145"
"2014-01" "" "171.554959785523" "397.631790670588" "707.674101782581" "372.614512238174" "" "155.732087227414" "391.236306729264" "271.340009537434" "331.618334892423" "190.831426392067"
"2014-02" "" "169.648365206663" "404.293533734589" "718.171296296296" "387.972356935015" "" "155.805806920079" "385.990338164251" "276.992936427851" "333.333333333333" "193.467336683417"
"2014-03" "" "174.539282250242" "378.936847466259" "729.115218115218" "387.859676783603" "" "160.48951048951" "385.759107397665" "268.853367277789" "338.898305084746" "194.083859850661"
"2014-04" "" "184.390547263682" "422.223914530671" "754.570333880679" "402.41935483871" "" "168.531468531469" "411.089513810986" "279.500880559636" "342.175170775171" "198.400210423242"
"2014-05" "" "189.420921997551" "413.393483709273" "801.0241404535479" "418.795180722892" "" "169.092772936955" "422.419179874241" "286.170212765957" "345.493827160494" "199.066689584049"
"2014-06" "" "197.202797202797" "404.24388600355" "783.871954619143" "428.703803839763" "" "167.806417674908" "416.411512553582" "288.288288288288" "348.37752668635" "200.389865374804"
"2014-07" "" "193.097781429745" "429.736486692324" "789.126853377265" "424.671385237614" "" "172.413793103448" "432.318145814913" "278.079303384603" "359.810658271937" "202.242227040808"
"2014-08" "" "196.365767878077" "408.176100628931" "843.253588516747" "422.958397534669" "" "166.945373467113" "435.344585145163" "282.347900599829" "365.378573479349" "197.359735973597"
"2014-09" "" "194.791252485089" "402.576489533011" "785.227867993714" "431.100033380242" "" "169.617863228926" "448.860497237569" "297.783933518006" "369.451697127937" "199.716048485037"
"2014-10" "" "192.81122150789" "402.220493313746" "854.80093676815" "435.940432962279" "" "168.970814132104" "445.790816326531" "295.880149812734" "364.927642322203" "203.959208158368"
"2014-11" "" "185.945273631841" "419.023821411134" "805.790960451977" "427.297008547009" "" "164.808531265148" "438.50138121547" "292.071846589863" "357.322176583025" "201.927489674162"
"2014-12" "" "186.656671664168" "406.577375810341" "837.8746594005451" "425.415663937602" "307.484915844771" "162.818327380908" "437.57292882147" "312.306709620084" "367.705662056305" "200.653158997376"
"2015-01" "" "193.535514764565" "423.558897243108" "776.595744680851" "431.912451690024" "309.980161190329" "172.79012345679" "442.225392296719" "298.181818181818" "368.889097486658" "201.346210125841"
"2015-02" "906.297513256819" "194.843827466534" "427.254171186289" "848.809888874687" "464.674789390549" "322.840847736794" "174.492385786802" "450.762829403606" "303.843807199512" "371.71506612996" "205.022060731897"
"2015-03" "959.023539668701" "194.843827466534" "446.208340503642" "863.321799307958" "484.76213592233" "331.269349845201" "178.965688712084" "458.894638598229" "313.401253918495" "372.843229763429" "212.486840718335"
"2015-04" "957.002457002457" "202.411714039621" "430.94944512947" "870.047164164811" "500" "343.381836945304" "175.636586806239" "476.472891445656" "323.897659227001" "382.424590332357" "206.877729257642"
"2015-05" "1016.94915254237" "203.729281767956" "462.365591397849" "856.470588235294" "503.46565847511" "339.7365532382" "180.684104627767" "490.181268882175" "325.885452509695" "385.740888534185" "213.810284898028"
"2015-06" "1017.998385795" "207.166388162177" "466.810333057227" "812.244897959184" "493.738334932121" "334.545454545455" "184.977081760334" "487.350104275287" "324.976787372331" "394.112005088999" "215.606788344541"
"2015-07" "1029.92311507937" "206.737400530504" "468.936384342391" "796.688660801564" "482.264218395367" "343.51106870229" "184.229918938836" "491.690382288352" "321.766848816029" "400.235849056604" "216.852207293666"
"2015-08" "1004.02252614642" "206.068329718004" "469.89247311828" "794.318181818182" "484.675577156744" "349.201596806387" "187.16577540107" "490.526315789474" "319.256756756757" "401.206636500754" "217.707736389685"
"2015-09" "998.688311688312" "205.273364485981" "470.028544243578" "828.8218267581479" "497.301587301587" "347.380410022779" "187.16577540107" "489.653943906717" "331.095725466586" "394.153791847953" "218.340611353712"
"2015-10" "1033.91254897005" "209.252286175363" "481.768087058274" "891.950565492655" "502.515371716042" "350.145350949868" "187.713310580205" "495.600991325898" "328.518744714836" "400" "217.468967777983"
"2015-11" "1031.58929976397" "209.425349087003" "480.540697105016" "915.857605177993" "508.861183386902" "359.078048780488" "186.519458332505" "492.700729927007" "329.153605015674" "405.130946018172" "220.487179487179"
"2015-12" "1061.87929717341" "208.180466038671" "472.706480304956" "915.857605177993" "509.668508287293" "362.662018746006" "184.234791500305" "496.614569536424" "322.041601323253" "405.873493975904" "220.522052205221"
"2016-01" "1046.69887278583" "211.231441165675" "472.706480304956" "915.857605177993" "486.551063066203" "369.061414989994" "185.542168674699" "487.630117163796" "319.444444444444" "403.097947214076" "214.993537268419"
"2016-02" "1061.87929717341" "221.609937758796" "472.706480304956" "934.511434511434" "495.763221153846" "373.788995112133" "188.118279569892" "509.425036390102" "329.275280898876" "411.37752703338" "223.720782791515"
"2016-03" "1080.2895495447" "222.42374278648" "490.171568627451" "939.985568693098" "507.894736842105" "386.42825607064" "193.342541436464" "530.503978779841" "343.234323432343" "414.746176720476" "222.417295424526"
"2016-04" "1034.90601193931" "224.032520325203" "486.425666516042" "960.802386875148" "513.265157919433" "386.189845474614" "193.342541436464" "535.140934519158" "342.372165112336" "422.261359633674" "224.692717996289"
"2016-05" "1048.75567269659" "222.589315525876" "486.140350877193" "942.349137931035" "521.048501417578" "385.507246376812" "196.477018964324" "537.5" "343.804233242266" "423.718220338983" "227.678571428571"
"2016-06" "1080.75040783034" "229.310344827586" "486.619997502653" "962.439732950726" "522.904871017814" "387.043189368771" "200.680272108844" "533.992861156913" "352.77945538662" "425.749545515057" "229.206963249516"
"2016-07" "1064.64123272746" "228.699551569507" "491.175072025957" "966.684578183772" "515.277108433735" "382.022471910112" "206.278095443593" "544.122448979592" "354.325913472183" "423.718220338983" "231.75355450237"
"2016-08" "1130.18867924528" "229.681978798587" "493.555568734356" "934.195064629847" "516.173434273916" "391.240310077519" "207.614523721709" "555.681653293468" "359.73597359736" "424.430641821946" "233.60462223467"
"2016-09" "1099.65237543453" "230.066445182724" "499.926470588235" "914.547304170905" "521.016920481897" "386.297376093294" "208.333333333333" "530.453257790368" "362.020275162925" "435.150310559006" "233.583076245042"
"2016-10" "1078.72823618471" "233.606557377049" "490.196078431373" "888.956494134319" "514.955141820725" "401.702370100273" "212.36689861533" "537.634408602151" "360.547645391153" "427.563942307692" "236.909202503888"
"2016-11" "1115.24163568773" "236.71160517187" "493.243853505217" "893.538675312645" "513.148343734998" "405.003470615456" "214.626865671642" "538.692026491687" "363.12984496124" "429.875608014297" "238.121296813863"
"2016-12" "1160.86235489221" "235.810810810811" "506.012065637066" "908.964237516869" "521.0173913043481" "409.429280397022" "211.86895810956" "554.112554112554" "363.12984496124" "427.062534038631" "237.137857589941"
"2017-01" "1151.05521198953" "233.59375" "526.77570549751" "910.521955260978" "520.5985014175779" "403.299398426654" "216.493594394031" "563.61316246742" "362.622036262204" "431.989366415596" "234.370855806788"
"2017-02" "1133.1775108949" "239.427860696517" "522.137486573577" "916.3720887416" "519.677002413869" "410.606200832948" "219.61252446184" "549.539170506912" "369.02530183727" "436.437902684685" "231.824610076971"
"2017-03" "1113.00688282611" "245.429977898332" "522.5840336134449" "984.001704692105" "536.950570117235" "393.143280266568" "227.272727272727" "551.283987915408" "372.334834834835" "435.391887349367" "234.220135628586"
"2017-04" "1116.95632218685" "248.607657881651" "549.215406562054" "1036.37413394919" "550.22392834293" "407.349665924276" "235.940660822657" "570.698466780239" "371.25340599455" "445.745271938207" "238.397965670693"
"2017-05" "1157.14285714286" "248.756218905473" "524.449911543565" "1034.48275862069" "555.1260977925469" "407.663316582915" "237.268396402263" "579.72027972028" "372.83017707821" "452.505966587112" "239.068549540235"
"2017-06" "1157.14285714286" "249.44099378882" "547.852495772991" "1011.24984979188" "555.1260977925469" "410.5743201256" "236.418918918919" "569.337216738352" "381.528776791873" "453.703703703704" "236.014917421417"
"2017-07" "1130.27862660216" "251.384888212653" "557.182705718271" "1014.6898146523" "556.082648113009" "386.735982750627" "236.742424242424" "591.232227488152" "390.004228281814" "465.502905266255" "237.187043493034"
"2017-08" "1092.71555538763" "251.831501831502" "549.903870498133" "1020.10644589001" "560.729716754681" "386.840202458424" "241.619585687382" "586.492890995261" "383.087765957447" "475.805041876571" "242.389515219842"
"2017-09" "1077.44107744108" "254.237288135593" "565.723270440252" "1000.9765625" "558.3850931677021" "391.509037503649" "241.619585687382" "593.791281373844" "382.671129846977" "476.417910447761" "249.508709245199"
"2017-10" "1139.59731543624" "261.343804537522" "571.739130434783" "1025.02662406816" "570.807453416149" "402.106821787414" "241.964827167981" "596.198156682028" "390.791428571429" "476.417910447761" "251.747387523745"
"2017-11" "1250.41868206168" "261.953204476094" "562.781456953642" "1050.8875739645" "578.918995130222" "398.204653622422" "238.095238095238" "629.62962962963" "404.368358913813" "482.955414012739" "252.96788842037"
"2017-12" "1233.18744339931" "265.432098765432" "565.595873163629" "1033.77425044092" "580.8080808080809" "391.791044776119" "250.446630888491" "614.285714285714" "405.672823218997" "489.038792922288" "251.817775792765"
"2018-01" "1231.63606010017" "265.939597315436" "567.6421412981" "1065.08875739645" "592.429123301946" "398.204653622422" "255.567928730512" "592.384519350812" "417.145137681292" "500.779626834864" "250.883179446006"
end



Here's the data that might help the explanation of the question. I'm looking to rename the variables B with San Francisco and C Pittsburgh - while keeping value label the same (zip code). Also of equal importance I'd need to have the City Name dropped as an observation. Or is there any way to run a time series but set TSSET to exclude observations (1/5) for instance? This way each additional column cell 1/5 that is string information regarding the City/Metropolitan within the column -- isn't instead interpreted as a (delta t) time series.

As it stands I have to drop all the observations that are words except for the single one I wan't to make the variable name.

PS: Is there a command for making the 1st observations of each column the varname directly? This workaround would at least be one solution.

Thanks for the time and any help.

[Robert Picard]

There's no zip code in the data. Also, column A and E cannot have the same variable name (same with G and J).

[William Lisowski]

Here is the problem. You have imported your data from an Excel worksheet.

The first row contained column headings with "RegionName" in A1 and 5-digit ZIP codes in B1 through L1. RegionName became the Stata variable name for the first column, but a numeric ZIP code cannot be a Stata variable name, so the remaining variables were named B through L.

The second row contained "City" in A2 and city names in B2 through L3; "State" in A3 and state codes in B3 through L3; "County Name" in A4 and county names in B4 through L4.

Then your data start with a month in column A and values in columns B through L.

The approach I would take would be something like the following, untested, outline.

I would go back to the worksheet and import it into two datasets. The first would contain the first four rows, all as string data. The second would contain rows 5 onward, the first column will be imported as a string and then converted to a SIF monthly date, while the remaining 11 columns will be imported as numeric data. In neither case would I attempt to get variable names from the worksheet: I would let the variable names be A through L in each case.

I would then reshape each of these datasets. For the first dataset it would be something like the following, where I reconstructed the data as I would expect it to look imported from Excel.

cls
// the following data would actually be imported from the Excel worksheet
clear
input str10 A str16(B C) str17(D E F) str16(G H I J K L)
"RegionName" "90001" "90002" "90003" "90004" "90005" "90006" "90007" "90008" "90010" "90011"
"City" "San Francisco" "Pittsburg" "Alameda" "San Francisco" "Fremont" "Hayward" "Antioch" "Fremont" "Hayward" "Union City" "Brentwood"
"State" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA" "CA"
"CountyName" "San Francisco" "Contra Costa" "Alameda" "San Francisco" "Alameda" "Alameda" "Contra Costa" "Alameda" "Alameda" "Alameda" "Contra Costa"
end
rename (B-L) (area_=)
rename A Geog
replace Geog = "ZIP" in 1
reshape long area_, i(Geog) j(column) string
reshape wide area_, i(column) j(Geog) string
rename (area_*) (*)
list if inlist(column,"D","E","F"), sepby(column) noobs

. list if inlist(column,"D","E","F"), sepby(column) noobs

  +--------------------------------------------------------+
  | column            City      CountyName   State     ZIP |
  |--------------------------------------------------------|
  |      D         Alameda         Alameda      CA   90003 |
  |--------------------------------------------------------|
  |      E   San Francisco   San Francisco      CA   90004 |
  |--------------------------------------------------------|
  |      F         Fremont         Alameda      CA   90005 |
  +--------------------------------------------------------+

Similarly, the numeric data, again reconstructing what I expect the data as it would look imported from Excel.

// the following data would actually be imported from the Excel worksheet
clear
input str8 A float (B C D E F G H I J K L)
"2013-08" . . . . 371.6906292702 . . . . . .
"2013-09" . . . . 373.953695458593 . . . . . .
"2013-10" . . 386.804657179819 730.366492146597 378.995578016425 . . . . . .
"2013-11" . . 383.534074276216 699.85 378.872145332032 . . 386.597257209507 263.189448441247 310.735346358792 189.298334174659
"2013-12" . 176.784523015344 386.029411764706 714.285714285714 377.06228956229 . 164.961496149615 390.354454810399 263.617196130736 322.060353798127 191.140278917145
end
rename (B-L) (value_=)
generate Month = monthly(A,"YM")
format Month %tm
drop A
order Month
reshape long value_, i(Month) j(column) string
rename value_ value
list if inlist(column,"D","E","F"), sepby(Month) noobs

. list if inlist(column,"D","E","F"), sepby(Month) noobs

  +-----------------------------+
  |   Month   column      value |
  |-----------------------------|
  |  2013m8        D          . |
  |  2013m8        E          . |
  |  2013m8        F   371.6906 |
  |-----------------------------|
  |  2013m9        D          . |
  |  2013m9        E          . |
  |  2013m9        F   373.9537 |
  |-----------------------------|
  | 2013m10        D   386.8047 |
  | 2013m10        E   730.3665 |
  | 2013m10        F   378.9956 |
  |-----------------------------|
  | 2013m11        D   383.5341 |
  | 2013m11        E     699.85 |
  | 2013m11        F   378.8721 |
  |-----------------------------|
  | 2013m12        D   386.0294 |
  | 2013m12        E   714.2857 |
  | 2013m12        F   377.0623 |
  +-----------------------------+

These are now suitable for merging.

I would leave the data in a long layout; it will almost certainly be more suitable for your eventual analysis.