Hello,

I have 115 worksheets (each of them has about 100 variables) from excel to work with and they are in a long form. Here is a sample of a worksheet once I imported into stata.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str27 var str91 varlabel long area double areapc
"U"    "community"              57601 1.37
"U1"   "city and mall"           4833  .11
"U201" "horizontal living area" 48731 1.16
"U3"   "government area"         2847  .07
"U501" "industrial area"          331  .01
end
Since I don't know how to reshape from long to wide while containing variable label, I ended up droping the varlabel and generating variable i = 1 before applying
reshape wide @area @areapc, i(i) j(var) string
. So, now my data looks like this (after I rename each variable by cutting off the word "area" after each variable),

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 U float(Upc U1 U1pc U201 U201pc U3 U3_pc U501 U501pc)
"57601 " 1.37 4833 .11 48731 1.16 2847 .07 331 .01
end

My questions is (1) How can I reshape (from long to wide form) the data while capturing variable label which is in a different column?
I found this thread https://www.statalist.org/forums/for...els-from-excel and its codes are


Code:
 
 clear* import excel label_test.xlsx, sheet("Sheet1") firstrow foreach v of var TT1-CODE2 { la var `v' "`=`v'[1]'" replace `v'="" in 1  } drop in 1 destring _all, replace
but it works when variable labels are in the different row (not in different column like my dataset).

Any advice would be really appreciated.
Thank you.
Pawinee