How do I reformat data from excel in bulk while importing?

[Hugo Brundin]

Hi,

I'm fairly new to Stata, so this question may be somewhat basic

I need to import a decent amount of data from excel into stata. In this case, I have country codes on the y-axis and years on the x-axis, with values in between, organized across several sheets. I'd like to import the data, while reformating it to have country code and year as variables, rather than having each year as a separate variable, which is how it turns out if try to import it now.

Any help is greatly appreciated,
Thanks

[William Lisowski]

Welcome to Statalist.

Once your data is imported, you will have one dataset for each worksheet in Excel, and each dataset will have what is known to Stata as a wide layout of your data - with separate columns for each year's values, and one observation for each country code.

You will then want to use the reshape long command to rearrange each dataset into a long layout - where you will have country code and year as variables, and one additional variable containing the data item for that combination of country code and year.

Then you will need to successively merge the datasets by country code and year to get a single dataset with country code, year, and all the additional variables.

Added in edit: you may find the user-written xls2dta command useful for converting all your Excel workshees to Stata datasets.

search xls2dta