How to set a dataset as a time series when the panel identifier is in the first column and the date is in the first row?

[Carl Kier]

The data I have consists of municipal data. The column consists of the name of the municipality. The first column consists of years and quarters. Then each cell contains the data for the specific municipality in the specific year and quarter. When importing such a dataset, each year and quarter and the name of each municipality is imported as separate variables. How do I, with such a dataset, set the panel ID and time variable properly?

[Nick Cox]

Data example please! https://www.statalist.org/forums/help#stata

[Carl Kier]

Data example please! https://www.statalist.org/forums/help#stata

Sorry about. I think it is easiest to see what I'm talking about if I show it visually. I imported a dataset in xls format. It is imported as shown in the following image:

[Nick Cox]

Sorry, but that is not easier to react to, at least for me. Specifically, I can't read it, and generally, we do explain at the link given in #1 that screenshots are almost always less help than you hope.

[Carl Kier]

Sorry, but that is not easier to react to, at least for me. Specifically, I can't read it, and generally, we do explain at the link given in #1 that screenshots are almost always less help than you hope.

I will try explain in more detail then, and give the output from dataex. I'm importing a dataset in xls format. The dataset consists of a list of municipalities, which is the first column, a list of years and quarters, which is the first row. Then, for each cell corresponding to a specific municipality and year and quarter, there is a datapoint (consisting of a number). Now, when importing this into Stata, each individual cell, including the first column consisting of the list of municipalities, and the first row consisting of a list of years and quarters, get imported as an individual string variable. What i want, obviously, is to have a single variable, consisting of the municipalities (converted to an ID number), and a single variable, consisting of the years and quarters, and a single variable consisting of the data corresponding to each each year and quarter and municipality. I don't know how to do this.
The Dataex output is as follows:

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 C str6(D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ BK BL BM)
data width (263 chars) exceeds max linesize. Try specifying fewer variables
r(1000);
.

[Andrew Musau]

Try

dataex C-O in 1/30

[Carl Kier]

Try

dataex C-O in 1/30

That gives the following output:

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 C str6(D E F G H I J K L M N O)
""                "2008K1" "2008K2" "2008K3" "2008K4" "2009K1" "2009K2" "2009K3" "2009K4" "2010K1" "2010K2" "2010K3" "2010K4"
"København"      "26852"  "26058"  "24502"  "22342"  "20825"  "20847"  "21954"  "22090"  "22775"  "24160"  "24260"  "24357" 
"Frederiksberg"   "39382"  "36224"  "32944"  "31799"  "24743"  "30706"  "27445"  "26027"  "32509"  "34462"  "32431"  "35141" 
"Dragør"         "26801"  "24872"  "26575"  "23058"  "22542"  "18738"  "20278"  "21627"  "21636"  "22044"  "21521"  "23537" 
"Tårnby"         "22341"  "22000"  "21295"  "19066"  "17070"  "17431"  "18028"  "18241"  "18108"  "19127"  "19467"  "19670" 
"Albertslund"     "18770"  "18378"  "17984"  "16401"  "15775"  "15327"  "15478"  "15831"  "16499"  "16128"  "15860"  "17309" 
"Ballerup"        "21067"  "21310"  "20585"  "17216"  "17168"  "17277"  "17137"  "18867"  "17852"  "19766"  "19143"  "19853" 
"Brøndby"        "19132"  "21214"  "19163"  "18290"  "17028"  "16143"  "16747"  "17001"  "17084"  "18105"  "18456"  "18519" 
"Gentofte"        "32308"  "30940"  "29845"  "27759"  "25799"  "26529"  "28160"  "28427"  "30179"  "31926"  "32184"  "31726" 
"Gladsaxe"        "26033"  "25073"  "22221"  "21267"  "19012"  "20066"  "20129"  "20384"  "20828"  "22168"  "21775"  "23196" 
"Glostrup"        "19490"  "18894"  "19701"  "19214"  "15429"  "18577"  "16744"  "17714"  "18918"  "18103"  "19782"  "17845" 
"Herlev"          "21129"  "21574"  "21193"  "16876"  "16294"  "17749"  "17692"  "17969"  "18488"  "20009"  "18945"  "20090" 
"Hvidovre"        "21907"  "21788"  "21048"  "18256"  "15973"  "16822"  "17874"  "17142"  "19088"  "19285"  "19285"  "18210" 
"Høje-Taastrup"  "19214"  "18714"  "18667"  "17223"  "14804"  "15089"  "16012"  "15592"  "16630"  "16589"  "17385"  "16201" 
"Ishøj"          "19185"  "18206"  "17054"  "16923"  "14747"  "14792"  "15789"  "15648"  "14173"  "15381"  "16471"  "16576" 
"Lyngby-Taarbæk" "28133"  "27327"  "25959"  "23517"  "21434"  "22186"  "23188"  "23704"  "25947"  "25726"  "26818"  "27480" 
"Rødovre"        "22371"  "21715"  "21156"  "19478"  "17898"  "17920"  "18091"  "17522"  "18575"  "19185"  "19306"  "20923" 
"Vallensbæk"     "20386"  "21129"  "20538"  "18575"  "16984"  "18046"  "17408"  "18991"  "18596"  "19632"  "19649"  "19096" 
"Allerød"        "22937"  "22076"  "20574"  "18559"  "17401"  "17100"  "18332"  "17919"  "19152"  "18757"  "20835"  "20390" 
"Egedal"          "19579"  "19853"  "18811"  "18053"  "16712"  "15649"  "16181"  "16549"  "15589"  "17229"  "17492"  "16437" 
"Fredensborg"     "22969"  "22240"  "22260"  "19515"  "17796"  "16622"  "17132"  "17363"  "17854"  "18184"  "18598"  "18895" 
"Frederikssund"   "17930"  "18220"  "16580"  "14914"  "13992"  "13942"  "13703"  "13062"  "14013"  "14656"  "14514"  "13590" 
"Furesø"         "25297"  "24072"  "22169"  "20831"  "18194"  "19692"  "19791"  "21727"  "20381"  "21253"  "21472"  "21777" 
"Gribskov"        "18150"  "17024"  "16605"  "14306"  "13204"  "13636"  "13222"  "13053"  "13051"  "13944"  "13745"  "13887" 
"Halsnæs"        "16331"  "16249"  "14471"  "12197"  "11630"  "11785"  "10992"  "11903"  "11965"  "11399"  "10426"  "11351" 
"Helsingør"      "24184"  "20943"  "21134"  "19238"  "16177"  "16394"  "17979"  "18491"  "17968"  "18223"  "19083"  "18023" 
"Hillerød"       "19425"  "20166"  "20302"  "17223"  "16490"  "15648"  "16889"  "15012"  "16150"  "17247"  "17345"  "17584" 
"Hørsholm"       "28742"  "27256"  "25555"  "25117"  "19488"  "21513"  "20945"  "24340"  "23278"  "24450"  "25188"  "24939" 
"Rudersdal"       "28855"  "29171"  "28588"  "23290"  "22837"  "22920"  "23861"  "25424"  "25443"  "26817"  "25855"  "26929" 
"Bornholm"        "7655"   "8137"   "7934"   "7512"   "7227"   "7312"   "7105"   "7249"   "7294"   "7432"   "6578"   "7614"  
end

[Clyde Schechter]

rename C location
foreach v of varlist D-O {
    local vname = "value`=quarterly(`v'[1], "YQ")'"
    rename `v' `vname'
}
drop in 1
reshape long value, i(location) j(qdate)
format qdate %tq
xtset location qdate
destring value, replace

Notes:
1. Replace D-O in the above by D- whatever is the last column of the spreadsheet.
2. It is possible that some of the values in the cells of your spreadsheet will not be understandable to Stata as numbers. For example, if missing values appear as "N/A" or "-" or something like that. Alternatively, there can just be typograhical errors, such as "123r5" If you run into that kind of problem, run

browse if missing(real(value))

to see the offending observations. Then write some -replace- statements to either fix up errors or replace these with missing string value (""), and then run -destring-.
3. You may want to give the variables location and value more informative, descriptive names.

[Nick Cox]

This is similar to the answer from Clyde Schechter but isn't restricted to the data example. It automatically loops over every variable but C.

ds C, not
local varlist `r(varlist)'

foreach v of local varlist {
    local date = `v'[1]
    local date : subinstr local date "k" "q", all
    rename `v' v`date'
}

drop in 1

reshape long v, i(C) j(work) string

gen qdate = quarterly(work, "YQ")
format qdate %tq
drop work

destring v, replace

egen id = group(C), label

tsset id qdate

list in 1/50

[Clyde Schechter]

Reading Nick's response, I realized that my own solution is incorrect in one place. Because location is a string variable, -xtset- will not accept it as the panel variable. So before the -xtset- command there needs to be either -encode location, gen(municipality)- or -egen municipality = group(location)-. Then -xtset municipality qdate- will run correctly.