My dta

[MuhammetAli Cansi]

I need help with creating long data in Stata. Below is the part of the image of my data . Now I tried using reshape however it did not work since the data types are given in rows not in columns. D-E-F ... are the years when I imported from Excel Stata named them as that. How can I turn it into long format where I have variables as Country Year Inflation rate .....I could not find a existing solution.

[Nick Cox]

Please back up and study FAQ Advice #12. https://www.statalist.org/forums/help#stata

Screenshots are not as helpful as you hope, as explained there.

Despite that general advice, it might help to see what the data look like in Excel and it should help to see exactly how you imported the data into Stata, that is, to see the exact command syntax.

Your data layout is puzzling as although there is a layout of countries and years in variables country year, there is also a layout of multiple values for each variable in variables D E F and following. Why would be there multiple values of (e.g.) current account balance for Argentina in 1983? Are these monthly values, or are the values for Argentina 1980 onwards in variables D E F onwards?

It's best to use Stata terminology on Statalist, so observations not rows, variables not columns.

[MuhammetAli Cansi]

Please back up and study FAQ Advice #12. https://www.statalist.org/forums/help#stata

Screenshots are not as helpful as you hope, as explained there.

Despite that general advice, it might help to see what the data look like in Excel and it should help to see exactly how you imported the data into Stata, that is, to see the exact command syntax.

Your data layout is puzzling as although there is a layout of countries and years in variables country year, there is also a layout of multiple values for each variable in variables D E F and following. Why would there be multiple values of (e.g.) current account balance for Argentina in 1983? Are these monthly values, or are the values for Argentina 1980 onwards in variables D E F onwards?

It's best to use Stata terminology on Statalist, so observations not rows, variables not columns.

Thank you for the warning let me repeat my question.


I have a panel data that includes 35 countries 43 years and 10 variables. However, when I gathered it online It appeared as the following I have the variables as observations in the variable "Data type" and years appeared as variables with names going from C.D., AZ. For importing I used the syntax. I just clicked on import and choose the file choose the option "Import first row as variable names" and then ımported the data.

import excel "C:\Users\alica\Desktop\Master Thesis Readins\datalar\New Microsoft Excel Worksheet.xlsx", sheet("Sheet2")

Now what I want to do is have total of 12 variables. One is for countries one is for years which I will use it to make a panel data. the rest are the variables that appear as observations under the variable Data types. In the screenshot, there is a mistake that I included the variable year while trying to figure out the problem on my own. I forgot to delete it sorry for the mistake.

Normally I would use the

reshape long year, i(Country) j(Variable names)

However it does not work since I have the data that I want to make variables as observations. Now is it possible to do it or do I have to fix it manually

[Nick Cox]

Thanks; there is a little more information in #3. I should have emphasised the most important detail in FAQ Advice #12, to use dataex to give an example.

With some more or less wild guesses, here is a fictitious example which you may be able to modify to fit your problem.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 country str23 datatype float(C D E F G)
"Freedonia" "Current account balance" 45 75 44 33 46
"Freedonia" "GNP per capita"          35 48 93 22 99
"Freedonia" "Something else"          18 51  6 96 76
"Sylvania"  "Current account balance"  4 16 96 94 42
"Sylvania"  "GNP per capita"          45 97 64 34 23
"Sylvania"  "Something else"          10 89 92 96 40
"Ruritania" "Current account balance" 42 88 18 89 29
"Ruritania" "GNP per capita"          72 59 63 28 93
"Ruritania" "Something else"          27  9 99  6 90
end

encode datatype,gen(DATATYPE)
drop datatype 
rename DATATYPE datatype 

su datatype, meanonly 
local nvars = r(max)
forval j = 1/`nvars' { 
    local label`j' : label (datatype) `j'
}

ds country datatype, not 

rename (`r(varlist)') whatever#, addnumber 

reshape long whatever, i(country datatype) j(year)

reshape wide whatever, i(country year) j(datatype)

forval j = 1/`nvars' { 
    label var whatever`j' "`label`j'' 
}

replace year = year + 1979 

list 

describe

Results

. list 

     +---------------------------------------------------+
     |   country   year   whatev~1   whatev~2   whatev~3 |
     |---------------------------------------------------|
  1. | Freedonia   1980         45         35         18 |
  2. | Freedonia   1981         75         48         51 |
  3. | Freedonia   1982         44         93          6 |
  4. | Freedonia   1983         33         22         96 |
  5. | Freedonia   1984         46         99         76 |
     |---------------------------------------------------|
  6. | Ruritania   1980         42         72         27 |
  7. | Ruritania   1981         88         59          9 |
  8. | Ruritania   1982         18         63         99 |
  9. | Ruritania   1983         89         28          6 |
 10. | Ruritania   1984         29         93         90 |
     |---------------------------------------------------|
 11. |  Sylvania   1980          4         45         10 |
 12. |  Sylvania   1981         16         97         89 |
 13. |  Sylvania   1982         96         64         92 |
 14. |  Sylvania   1983         94         34         96 |
 15. |  Sylvania   1984         42         23         40 |
     +---------------------------------------------------+

. 
. describe 

Contains data
 Observations:            15                  
    Variables:             5                  
-------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
-------------------------------------------------------------------------------
country         str9    %9s                   
year            int     %10.0g                
whatever1       float   %9.0g                 Current account balance
whatever2       float   %9.0g                 GNP per capita
whatever3       float   %9.0g                 Something else
-------------------------------------------------------------------------------
Sorted by: country
     Note: Dataset has changed since last saved.

[MuhammetAli Cansi]

Thanks; there is a little more information in #3. I should have emphasised the most important detail in FAQ Advice #12, to use dataex to give an example.

With some more or less wild guesses, here is a fictitious example which you may be able to modify to fit your problem.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 country str23 datatype float(C D E F G)
"Freedonia" "Current account balance" 45 75 44 33 46
"Freedonia" "GNP per capita" 35 48 93 22 99
"Freedonia" "Something else" 18 51 6 96 76
"Sylvania" "Current account balance" 4 16 96 94 42
"Sylvania" "GNP per capita" 45 97 64 34 23
"Sylvania" "Something else" 10 89 92 96 40
"Ruritania" "Current account balance" 42 88 18 89 29
"Ruritania" "GNP per capita" 72 59 63 28 93
"Ruritania" "Something else" 27 9 99 6 90
end

encode datatype,gen(DATATYPE)
drop datatype
rename DATATYPE datatype

su datatype, meanonly
local nvars = r(max)
forval j = 1/`nvars' {
local label`j' : label (datatype) `j'
}

ds country datatype, not

rename (`r(varlist)') whatever#, addnumber

reshape long whatever, i(country datatype) j(year)

reshape wide whatever, i(country year) j(datatype)

forval j = 1/`nvars' {
label var whatever`j' "`label`j''
}

replace year = year + 1979

list

describe

Results

. list

+---------------------------------------------------+
| country year whatev~1 whatev~2 whatev~3 |
|---------------------------------------------------|
1. | Freedonia 1980 45 35 18 |
2. | Freedonia 1981 75 48 51 |
3. | Freedonia 1982 44 93 6 |
4. | Freedonia 1983 33 22 96 |
5. | Freedonia 1984 46 99 76 |
|---------------------------------------------------|
6. | Ruritania 1980 42 72 27 |
7. | Ruritania 1981 88 59 9 |
8. | Ruritania 1982 18 63 99 |
9. | Ruritania 1983 89 28 6 |
10. | Ruritania 1984 29 93 90 |
|---------------------------------------------------|
11. | Sylvania 1980 4 45 10 |
12. | Sylvania 1981 16 97 89 |
13. | Sylvania 1982 96 64 92 |
14. | Sylvania 1983 94 34 96 |
15. | Sylvania 1984 42 23 40 |
+---------------------------------------------------+

.
. describe

Contains data
Observations: 15
Variables: 5
-------------------------------------------------------------------------------
Variable Storage Display Value
name type format label Variable label
-------------------------------------------------------------------------------
country str9 %9s
year int %10.0g
whatever1 float %9.0g Current account balance
whatever2 float %9.0g GNP per capita
whatever3 float %9.0g Something else
-------------------------------------------------------------------------------
Sorted by: country
Note: Dataset has changed since last saved.

Thank you so much