Help batch converting, making long, and merging BLS KLEMS series (multiple sheets within book)

[Robert Roussel]

Hi all, I am having some issues automating a process to crop, reshape, and merge datafilee that are on multiple different sheets of the same workbook.

Basically each sheet contains panel data in wide format and has a few lines at the top with the name of the data, but otherwise the data keeps the same cell array.

So basically the loop should do something like: 1) import each sheet, use the string in that first line to populate a new variable that helps indicate the name of the table from where the data comes from; 2) crop the selection to a specified array; 3) reshape it to long form; and then 4) save it as a .dta file.

Are there any elements of this loop that appear, right off the bat, impossible or far too complicated? If not, how would this loop like? I’ve tried to replicate many things I’ve found in other threads, but nothing really works all that well.

Thank you all so much

[Phil Bromiley]

I often find it easier to clean excel files manually (removing extra lines at the top so the first line is the variable name and second start the data etc.) rather than trying to program Stata to do it for me. If you really have an immense number of worksheets, then programming might be worth the trouble.

The problem as I see it is the junk at the top of such files. You might be able to work around this using the cellrange option with import. If you can get it clean into Stata, reshaping is not that hard.

[Phil Bromiley]

See: https://www.statalist.org/forums/for...hape-and-merge

[Robert Roussel]

Hi, thanks for all the good advice and that link, Phil!! I will just clean up the data in excel and then proceed in STATA.

[David Benson]

This post on "How Can I Read Data From Multiple Excel Sheets In Stata?" here might also give you some useful ideas. (But I think the code provided at the link mentioned by Phil (with some modifications) will do what you need.