reshaping based on year month country for multiple files

[Ritika Khurana]

Hi all,

I want to reshape 20 similar files that have same months, countries and years (2000 to 2017). The dataset I have is wide. Here, I have pasted data for just one variable (var).

I tried several approaches.
1). Transposing this matrix and then appending them. But, it didn't reshape.
2). Made one variable for month and year as one variable (monthyear), then reshaped, didn't work.
3). Tried joining all the 20 files together and then reshaped. Even that didn't work.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 Date str3 var int Year str3 Month double(Austria Belgium Cyprus Estonia Finland France Germany Greece Ireland Italy Latvia Lithuania Luxembourg Malta Netherlands Portugal Slovakia Slovenia Spain xEuro)
"2000-01-01" "UNE" 2000 "Jan" 4.1 6.4 3.9 12.6 8.4 8.8  8.1 8.9 4.3 8.4 13.2 13.8 1.9   5   3 4.3 14.8 5.4 10.6 8.2
"2000-02-01" "UNE" 2000 "Feb"   4 6.1   4 13.7 8.5 8.6    8   9 4.1 8.4 13.2 13.9 1.9   5   3   4   15 5.4 10.4 8.1
"2000-03-01" "UNE" 2000 "Mar" 3.9 5.9 4.2   13 8.5 8.5    8   9   4 8.4 13.2 14.1 1.9   5 2.9 3.9 15.2 5.5 10.3 8.1
"2000-04-01" "UNE" 2000 "Apr" 3.6 5.9 4.6 13.2 8.3 8.5  7.9   9 4.1   8 13.2 14.6 1.9 4.9 2.9 3.8 15.3 5.7 10.3   8
"2000-05-01" "UNE" 2000 "May" 3.5 5.8 4.6 12.7 8.1 8.4  7.9 8.9   4   8 13.3   15 1.9 4.8 2.8 3.8 15.6 5.8 10.2 7.9
"2000-06-01" "UNE" 2000 "Jun" 3.4 5.7 4.2 12.9 7.9 8.3  7.9 8.9 3.9   8 13.3 15.2 1.9 4.7 2.8 3.8 15.9 5.9 10.2 7.9
"2000-07-01" "UNE" 2000 "Jul" 3.6 5.6 3.9 12.7 7.8 8.2  7.8 8.8 3.8 7.8 13.3 15.3 1.9 4.6 2.7 3.8 16.2 5.7 10.2 7.8
"2000-08-01" "UNE" 2000 "Aug" 3.6 5.7   4 13.1 7.9 8.1  7.8 8.8 3.6 7.8 13.2 15.3 1.9 4.6 2.7   4 15.2 5.5   10 7.8
"2000-09-01" "UNE" 2000 "Sep" 3.6 5.7 4.2   13 7.9   8  7.8 8.7 3.5 7.8 13.3 15.2 1.8 4.5 2.6   4 15.1 5.3  9.9 7.7
"2000-10-01" "UNE" 2000 "Oct" 3.5 5.8 4.3 12.7 7.9   8  7.7 8.2 3.3 7.6 13.4 14.9 1.7 4.4 2.6 3.9 15.2 5.2  9.8 7.6
"2000-11-01" "UNE" 2000 "Nov" 3.4 5.7 4.1 13.1 7.8 7.9  7.7 8.3 3.3 7.6 13.3 14.9 1.6 4.4 2.5 3.6 15.4   5  9.6 7.6
"2000-12-01" "UNE" 2000 "Dec" 3.4 5.5   4 12.9 7.7 7.9  7.7 8.3 3.3 7.6 13.2 15.2 1.5 4.3 2.5 3.7 15.4   5  9.4 7.5
"2001-01-01" "UNE" 2001 "Jan" 3.4 5.2 3.5   13 7.9 7.8  7.6 8.3 3.3 7.5   13 16.2 1.4 4.2 2.5 3.7 15.7 4.8  9.1 7.5
"2001-02-01" "UNE" 2001 "Feb" 3.4 5.1 3.2 12.6 7.9 7.8  7.6 8.3 3.3 7.5 12.7 16.3 1.4 4.1 2.4 3.8 15.6 4.8  8.9 7.4
"2001-03-01" "UNE" 2001 "Mar" 3.5 5.1 3.1 12.7 7.5 7.7  7.6 8.3 3.2 7.5 12.4 16.2 1.4   4 2.4 3.8 15.6 4.7  8.9 7.4
"2001-04-01" "UNE" 2001 "Apr" 3.6 5.4 3.5   13 7.4 7.7  7.6 8.4 3.1 7.4   12   16 1.4   4 2.4 3.8 15.8 4.6  8.8 7.4
"2001-05-01" "UNE" 2001 "May" 3.7 5.4 3.8 12.4 7.6 7.6  7.6 8.4 3.1 7.4 11.9 15.9 1.4   4 2.4 3.8 15.7 4.6    9 7.4
"2001-06-01" "UNE" 2001 "Jun" 3.8 5.3 3.6 11.7 7.4 7.6  7.7 8.4 3.2 7.4 11.8 15.7 1.4 3.9 2.3 3.8 15.7 4.6  8.8 7.4
"2001-07-01" "UNE" 2001 "Jul" 3.7   5 3.3 12.1 7.2 7.5  7.7 8.5 3.4 7.3 11.9 15.4 1.4   4 2.3   4 15.7 4.6  8.8 7.4
"2001-08-01" "UNE" 2001 "Aug" 3.7   5 3.1 11.6 7.5 7.5  7.8 8.4 3.5 7.3   12 15.3 1.5 4.1 2.4 3.9 15.8 4.7  8.9 7.4
"2001-09-01" "UNE" 2001 "Sep" 3.8 5.4 3.3 11.8 7.8 7.5  7.8 8.4 3.5 7.3   12 15.4 1.5 4.1 2.4 3.8 15.9 4.8  8.8 7.4
"2001-10-01" "UNE" 2001 "Oct" 3.8 5.9 3.5 10.7 7.5 7.4  7.9 8.7 3.4 7.2 12.7 16.1 1.6 4.1 2.4 3.8   16 4.9  8.8 7.4
"2001-11-01" "UNE" 2001 "Nov" 3.9 6.1 3.4   11 7.8 7.4  7.9 8.7 3.4 7.2 12.8 16.1 1.6 4.1 2.4 3.8 15.9 5.1  8.9 7.4
"2001-12-01" "UNE" 2001 "Dec" 4.1 6.2 3.1 10.7 7.4 7.4    8 8.7 3.4 7.2 12.4 15.9 1.6 4.2 2.5 3.8 15.6 5.2  9.1 7.5
"2002-01-01" "UNE" 2002 "Jan" 4.2 6.1 2.7 10.7 7.5 7.3    8 8.6 3.5 6.9 11.8 15.6 1.7 4.5 2.5 3.9 15.5 5.3  9.4 7.4
"2002-02-01" "UNE" 2002 "Feb" 4.2 6.1 2.5 10.8 7.5 7.2  8.1 8.6 3.5 6.9 11.6   15 1.7 4.7 2.6 3.9 15.4 5.3  9.8 7.5
"2002-03-01" "UNE" 2002 "Mar" 4.1 6.1 2.5 10.5 7.1 7.2  8.1 8.6 3.6 6.9   12 14.2 1.8 4.7 2.6 4.1 15.3 5.3  9.7 7.5
"2002-04-01" "UNE" 2002 "Apr" 3.9 6.1 2.9  9.9 7.4 7.2  8.2 8.3 3.5 7.2 12.5 13.2 1.9 4.6 2.7 4.2 15.3 5.1  9.7 7.5
"2002-05-01" "UNE" 2002 "May" 3.8 6.2 3.1  9.7 7.9 7.2  8.2 8.3 3.6 7.2 12.5 12.4 2.1 4.6 2.8 4.4 15.4   5  9.7 7.6
"2002-06-01" "UNE" 2002 "Jun" 3.8 6.2 2.9 10.4 6.9 7.2  8.3 8.3 3.6 7.2   12 11.9 2.1 4.6 2.9 4.7 15.4 4.9  9.8 7.6
"2002-07-01" "UNE" 2002 "Jul"   4 6.3 2.8 10.7 7.5 7.3  8.5 8.3 3.5   7 10.6 11.8 2.2 4.8 2.9 4.9 15.5 4.8 10.1 7.7
"2002-08-01" "UNE" 2002 "Aug"   4 6.3 2.9 10.5 7.5 7.3  8.6 8.3 3.5   7 10.2 11.8 2.3   5   3 4.8 15.5 4.8 10.2 7.7
"2002-09-01" "UNE" 2002 "Sep" 4.1 6.5 3.2 10.6 7.5 7.3  8.7 8.3 3.7   7 10.3 11.8 2.3 4.9 3.1   5 15.5   5 10.1 7.8
"2002-10-01" "UNE" 2002 "Oct" 4.1 6.7 3.5   11 7.6 7.2  8.8 8.2   4 6.9 11.1   12 2.4 4.8 3.2 5.4 15.4 5.2  9.9 7.8
"2002-11-01" "UNE" 2002 "Nov" 4.1 6.9 3.4 10.7   7 7.3  8.9 8.1 4.2 6.9 11.4 11.9 2.5 4.8 3.3 5.6 15.4 5.2  9.9 7.8
"2002-12-01" "UNE" 2002 "Dec" 4.1 6.8 3.3   11 7.4 7.5  9.1 8.1 4.1 6.9   11 11.8 2.5 4.8 3.3 5.6 15.4 5.2  9.9 7.9
"2003-01-01" "UNE" 2003 "Jan" 4.1 6.7   3 10.4 7.5 7.1  9.2 7.9 3.9 7.1 10.3 11.7 2.6   5 3.3 5.7 15.2 5.2   10 7.9
"2003-02-01" "UNE" 2003 "Feb" 4.2 6.6 2.9 10.2 6.9 7.1  9.3 7.9 3.8 7.1   10 11.5 2.7   5 3.4 5.8 15.1 5.3 10.1 7.9
"2003-03-01" "UNE" 2003 "Mar" 4.2 6.6 3.2  9.8 7.3 7.1  9.4 7.9 3.6 7.1 10.1 11.5 2.8 4.9 3.6 5.8   15 5.5   10   8
"2003-04-01" "UNE" 2003 "Apr" 4.3 6.7 3.7  9.4 7.6 7.2  9.5 7.9 3.7 6.7 10.5 11.3 2.9 4.7 3.7 5.9 14.7 5.6  9.9 7.9
"2003-05-01" "UNE" 2003 "May" 4.4 6.7 3.8  9.7 7.4 7.2  9.5 7.9 3.7 6.7   11 11.3   3 4.7 3.7   6 14.5 5.6  9.8 7.9
"2003-06-01" "UNE" 2003 "Jun" 4.4 6.7 3.7  9.1 7.7 7.2  9.5 7.9 3.8 6.7 11.3 11.2 3.1 4.8 3.8   6 14.4 5.6  9.9   8
"2003-07-01" "UNE" 2003 "Jul" 4.3 6.6 3.6  8.6   7 7.2  9.5 7.8   4 6.8 11.3 11.2 3.2 5.5 3.9 5.9 14.5 5.5   10   8
"2003-08-01" "UNE" 2003 "Aug" 4.3 6.6 3.5  8.3 6.7 7.2  9.5 7.8   4 6.8 11.2 11.1 3.3 5.7   4 5.8 14.8 5.5   10   8
"2003-09-01" "UNE" 2003 "Sep" 4.3 6.7 3.8    8 7.3 7.3  9.5 7.8 3.9 6.8 10.9 10.9 3.4 5.4 4.1 5.9 15.1 5.4  9.9   8
"2003-10-01" "UNE" 2003 "Oct" 4.4 6.7 4.1  8.3 7.2 7.4  9.5   8 3.7 6.8 10.3 10.7 3.5 5.5 4.2 5.9 15.2 5.5  9.8   8
"2003-11-01" "UNE" 2003 "Nov" 4.5 6.8 3.9  8.2 7.1 7.5  9.5   8 3.7 6.8  9.9 10.6 3.6 5.4 4.3 5.9 15.6 5.4  9.7   8
"2003-12-01" "UNE" 2003 "Dec" 4.6   7 3.8  8.5 7.3 7.6  9.5 8.1 3.8 6.8   10 10.5 3.7 5.2 4.4 5.7 16.1 5.3  9.8   8
"2004-01-01" "UNE" 2004 "Jan" 4.4 7.3 3.4  8.5 7.3 7.5  9.5 9.3 4.1 6.7 10.5 10.4 3.9   5 4.4 5.6 16.4 5.2  9.7 8.1

Can someone please suggest me how do I approach this reshape? Thank you!

Best,

Ritika

[Øyvind Snilsberg]

foreach v of var Austria-xEuro {
rename `v' une`v'
}

reshape long une, i(Date) j(country) s

[Ritika Khurana]

Thank you, Oyvind Snilsberg. It worked.