Two row header data

[Syazadzani Bat]

Hello, I need help. Im pretty new at stata. Im confused how to reshape this from excel import


The original data is

ENTITY	VAR1			VAR2			VAR3
	1990	1991	1992	1990	1991	1992	1990	1991	1992
A	VAL1	-	VAL2	-	VAL3	VAL4	..and so on
B

to this

ENTITY	YEAR	VAR1	VAR2	VAR3
A	1990	VAL1	-
	1991	-	VAR3
	1992	VAL2	VAR4
B	1990
	1991
	1992
C	...and so on

Thank you

[Nick Cox]

This may help. Note that 8 will need changing for your real dataset, and some other commands too.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 var1 str4(var2 var3 var4 var5 var6 var7) str11 var8
"ENTITY" "VAR1" ""     ""     "VAR2" ""     ""     "VAR3"
"1990"   "1991" "1992" "1990" "1991" "1992" "1990" "1991"
"A"      "1"    "-"    "2"    "-"    "3"    "4"    "5"   
"B"      ""     ""     ""     ""     ""     ""     ""    
end

forval j = 2/8 { 
    local J = `j' - 1 
    replace var`j' = var`J' in 1 if missing(var`j')
    rename var`j' var`j'`=var`j'[2]'
}

drop in 2 

list 

unab varlist : *
tokenize "`varlist'"

forval j = 2/8 {
    local year = substr("``j''", -4, 4)
    rename var`j' `=var`j'[1]'`year'
}

drop in 1 
rename var1 ENTITY 

list 

reshape long VAR1 VAR2 VAR3, i(ENTITY) j(year)
destring *, replace ignore("-")



. list

     +------------------------------------+
     | ENTITY   year   VAR1   VAR2   VAR3 |
     |------------------------------------|
  1. |      A   1990      2      4      . |
  2. |      A   1991      1      .      5 |
  3. |      A   1992      .      3      . |
  4. |      B   1990      .      .      . |
  5. |      B   1991      .      .      . |
     |------------------------------------|
  6. |      B   1992      .      .      . |
     +------------------------------------+

If this doesn't help enough, please give a concrete data example, not a half-abstracted one.