Export data set in a specific way

[Oliver Henrich]

Hi guys!

I am using the eurostatuse command to automatically get some data from Eurostat. However, I would like to export it in a certain way now, so I have the time in rows and the geographical location in columns (see pictures attached). Is there any easy way to do this?

Thanks a lot for your help!

[Nick Cox]

Photos are unreadable (at least by me). The only value of photo attachments here is optionally to allow personal pictures.

Please read and act on http://www.statalist.org/forums/help#stata

You can attach Stata graphs or other images. Note, however, that Stata graphs and other images are highly readable when inserted as .png file attachments (start with the Clipboard icon) and far less readable if inserted as photos (using the Camera icon).

Screenshots are possible but often do not help much. Even if they are legible, and they often are not, they do not allow copy and paste.

[Oliver Henrich]

I dont understand. When I click on the pictures they get enlarged and are quite visible.

[Nick Cox]

Computers differ. Some people read Statalist on smart phones, tablets, or machines with small monitors. You cut your readership by posting such attachments.

[Sarah Edgington]

I can see your pictures but it's not clear to me what the relationship is between the variables and observations in the Stata data and the elements in the spreadsheet.
I suspect you want to reshape your data so starting with that help file and manual entry might point you in the right direction.
Otherwise you'll probably want to provide some more details about what you have and what you want so we can help you more easily.

[Oliver Henrich]

Thanks for the comment Sarah. Indeed I would like to reshape the data. I would like to have the time in rows on the left and the countries in columns on the top. Is there a simple code to do that?

[Alberto Nunez]

I think you may reshape long. As you have a geo() variable you may also want to generate new variables for your purpose. A loop may be the best way to get what you expect.
Something like
foreach i of geo {
gen `i'=`i'
}

[Nick Cox]

Alberto's code if taken literally would just be illegal code as

gen geo = geo

will fail because geo is an existing variable name.

I can't suggest the more general idea that he has in mind as I can't read the attachments myself (#2, #4).

[Sebastien Fontenay]

Dear Oliver,

From the pictures you sent, I understand that you would like to obtain time series for each country in a separate column.

Data from Eurostat are in wide format with each column a separate time period. The package eurostatuse (that we created with Sem Vandekerckhove) allows you to reshape the data to long format (i.e. panel data) but you should reshape once more to obtain time series.

This would be easy if you only have two dimensions, that is time and geo:

eurostatuse une_ltu_a, noflags nolabel long geo(BE DE FR) keepdim(LTU_ACT_RT ; T) clear
reshape wide une_ltu_a, i(time) j(geo, string)

But what if you have more dimensions? e.g. female, male and total unemployment rates. Then you could create a unique identifier for each series, for instance by concatenating the codes found in the variables:

eurostatuse une_ltu_a, noflags nolabel long geo(BE DE FR) keepdim(LTU_ACT_RT) clear
gen series=geo+"_"+sex
drop geo sex
reshape wide une_ltu_a, i(time) j(series, string)

Problem is that with these two methods, you would lose the labels attached to the variables. But since your goal is ultimately to export the data to Excel, this is probably not important.

I will definitely try, in a next release, to add an option to obtain ready-to-use time series.

Sébastien Fontenay.