Convert datastream wide data to long format

[ivanpersie]

Hi guys, i have attached a sample excel file with data as it would appear downloaded from Datastream. I would like to know how to change it from wide format to long format. FYI i have about 1000 companies in one worksheet

.

[Justin Niakamal]

Next time post a sample of your data using dataex (see the FAQ for instructions).

import excel "Returns.xlsx", sheet("Sheet2") cellrange(A4) clear

ren A name 
ds name, not
ren (`r(varlist)') (v#), addnumber

tempfile temp 

preserve 
keep in 1 
reshape long v, i(name) j(var)string 
ren v title 
save `temp'
restore 

drop in 1

reshape long v, i(name) j(var)string
merge m:1 var using `temp', keep(match) nogen 
drop if inlist(lower(name), "currency", "code") 

gen date = date(name, "MDY")
format date %td 
drop var name 

destring v, replace

[ivanpersie]

Thank you Justin. You have been so helpful and thanks for the "dataex tip"