Help with reshaping the data

[ANASTASSIA VILDERSON]

Dear community,

I am trying to bring some data into a different format. The data is World Uncertainty Index, which is publicly available for research purposes (https://worlduncertaintyindex.com/). The data provided is in the format that doesn't fit my overall dataset with other variables, so I've been trying to reshape it but no luck.

Current data format is

Period	AFG	AGO	ALB	ARE
2009q1	var1 value	var1 value	var1 value	var1 value
2009q2	var1 value	var1 value	var1 value	var1 value
2009q3	var1 value	var1 value	var1 value	var1 value
2009q4	var1 value	var1 value	var1 value	var1 value

where three-letter codes are ISO-codes for countries.

What I am trying to get is as follows:

Country	Period	Var1
AFG	2009q1	value
AFG	2009q2	value
AFG	2009q3	value
AFG	2009q4	value
ARE	2009q1	value
ARE	2009q2	value
ARE	2009q3	value

Thank you in advance for any advice!

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 year byte(AFG AGO) double ALB byte ARE double ARG byte ARM double AUS
"1952q1" . .  . .   1 .         0
"1952q2" . .  . .   0 .         2
"1952q3" . .  . .   0 .         0
"1952q4" . .  . .   0 .         0
"1953q1" . .  . .   0 .         1
"1953q2" . .  . .   0 .         2
"1953q3" . .  . .   0 .         0
"1953q4" . .  . .   1 .         0
"1954q1" . .  . . 1.5 .         0
"1954q2" . .  . .   2 .         2
"1954q3" . .  . .   0 .         2
"1954q4" . .  . .   0 .         2
"1955q1" . .  . .   2 .         1
"1955q2" . .  . .   0 .         0
"1955q3" . .  . .   1 .         3
"1955q4" . .  . .   1 .         5
"1956q1" 0 .  0 .   0 .         0
"1956q2" 0 .  0 .   0 .         2
"1956q3" 0 .  0 .   1 .         2
"1956q4" 0 .  1 .   2 .         2
"1957q1" 0 . .5 .   0 .         0
"1957q2" 0 .  0 .   0 . .66666667
"1957q3" 0 .  0 .   0 . 1.3333333
"1957q4" 0 .  0 .   0 .         2
"1958q1" 1 .  0 .   0 .         1
"1958q2" 0 .  0 .   0 .         1
"1958q3" 0 .  0 .   1 .         3
"1958q4" 0 .  0 .   1 .         1
"1959q1" 0 .  0 .   0 .         0
"1959q2" 0 .  0 .   0 .         0
"1959q3" 0 .  0 .   0 .         0
"1959q4" 0 .  0 .   2 .         0
"1960q1" 0 .  0 .   0 .         1
"1960q2" 0 .  0 .   2 .         3
"1960q3" 0 .  0 .   2 .         0
"1960q4" 0 .  0 .   0 .         0
"1961q1" 0 .  0 .   0 .         0
"1961q2" 0 .  0 .   1 .         1
"1961q3" 0 .  0 .   0 .         2
"1961q4" 0 .  0 .  .5 .         1
"1962q1" 0 .  0 .   1 .         5
"1962q2" 0 .  0 .   0 .         4
"1962q3" 0 .  0 .   1 .         1
"1962q4" 0 .  0 .   1 .         0
"1963q1" 0 .  0 .   4 .         3
"1963q2" 0 .  0 .   3 .         0
"1963q3" 0 .  0 .   2 .         0
"1963q4" 1 .  0 .   0 .         0
"1964q1" 0 .  0 .   1 .         0
"1964q2" 0 .  0 .   0 .         0
"1964q3" 0 .  0 .   3 .         0
"1964q4" 1 .  0 .   3 .         2
"1965q1" 1 .  0 .   1 .         0
"1965q2" 0 .  0 .   1 .         2
"1965q3" 0 .  0 .   0 .         0
"1965q4" 0 .  0 .   0 .         0
"1966q1" 0 .  0 .   1 .         0
"1966q2" 2 .  0 .   2 .         0
"1966q3" 0 .  0 .   3 .         0
"1966q4" 0 .  0 .   1 .         0
"1967q1" 0 .  0 .   1 .         0
"1967q2" 0 .  0 .   0 .         1
"1967q3" 0 .  0 .   0 .         1
"1967q4" 0 .  0 .   0 .         0
"1968q1" 0 .  0 .   0 .         1
"1968q2" 0 .  0 .   1 .         1
"1968q3" 0 .  0 .   1 .         1
"1968q4" 0 .  0 .   0 .         1
"1969q1" 0 .  0 .   2 .         1
"1969q2" 0 .  0 .   0 .         3
"1969q3" 0 .  0 .   0 .         3
"1969q4" 0 .  0 .   0 .         0
"1970q1" 0 .  0 .   0 .         0
"1970q2" 1 .  0 .   0 .         1
"1970q3" 0 .  0 .   1 .         0
"1970q4" 0 .  0 .   5 .         2
"1971q1" 0 .  0 0   3 .         4
"1971q2" 0 .  0 0   2 .         0
"1971q3" 1 .  0 0   0 .         3
"1971q4" 0 .  0 0   1 .         3
"1972q1" 0 .  0 1   3 .         3
"1972q2" 0 .  0 0   3 .         0
"1972q3" 0 .  0 0   4 .         3
"1972q4" 0 .  0 0   1 .         0
"1973q1" 0 .  1 0   3 .         2
"1973q2" 0 .  0 0   2 .         8
"1973q3" 0 .  0 1   1 .         2
"1973q4" 0 .  0 0   3 .         1
"1974q1" 3 0  0 1   1 .         0
"1974q2" 0 0  0 0   1 .         0
"1974q3" 0 0  0 0   3 .         2
"1974q4" 0 1  0 0   3 .         3
"1975q1" 0 0  0 0   1 .         0
"1975q2" 0 0  0 0   2 .         2
"1975q3" 0 0  0 0   0 .         2
"1975q4" 0 0  0 0   1 .         1
"1976q1" 0 0  0 0   1 .         0
"1976q2" 0 0  0 0   2 .         2
"1976q3" 0 1  1 0   0 .         0
"1976q4" 0 2  0 0   1 .         2
end

[Hemanshu Kumar]

rename (AFG-AUS) var1=
reshape long var1, i(year) j(country) string

* optional: create a time variable that Stata understands
gen quarter = quarterly(year,"YQ") , after(year)
format %tq quarter
drop year

[ANASTASSIA VILDERSON]

Thank you so much, Hemanshu Kumar!! This saved me so many hours!!

[Clyde Schechter]

ds year, not
rename (`r(varlist)') var1=
reshape long var1, i(year) j(country) string

Note: I used the -ds year, not- command on the assumption that all of the other variable names beside year are countries. If there are, in fact, other variables in the data set, then some other method of gathering the country names together will be needed. It might be something like -rename (AFG-ZWE) var1=- instead, if the country variables are all located in a consecutive block in your data set.

Your variable year is problematic. First, the name is just plain wrong: the information is a quarterly date, not a year. That's legal, but likely to create confusion somewhere along the way. It is also not really suitable for data analysis because you have it as a string. Although it does not affect the current problem, I strongly urge you to fix this by doing:

gen int qdate = quarterly(year)
assert missing(qdate) == missing(year)
format qdate %tq
drop year

Added: Crossed with #2 (which gives essentially the same advice) & #3.