This paper outlines a panel data retrieval program written for Stata/SE 10 or better, which allows easier accessing of complex panel data sets. Using a drop-down menu and mouse click system, the researcher selects variables from any and all available years of a panel study. The data is automatically retrieved and merged to form a "long file", which can be directly used by the Stata panel estimators. The system implements modular data cleaning programs called "plugins". Yearly updates to the data retrievals can be made automatically. Projects can be stored in libraries allowing modular administration and appending. The paper exemplifies the power of PanelWhiz using the example of the German SOEP (German Socio-Economic Panel Study).Panel data, storage, retrieval

John P. Haisken-DeNew

Markus H. Hahn

English

Research Papers in Economics

Deutsches Institut für 
Wirtschaftsforschung
www.diw.de
John P. Haisken-DeNew • Markus H. Hahn
PanelWhiz:
Efficient Data Extraction of Complex Panel  
Data Sets – An Example Using the German SOEP
53
Data Documentation
Berlin, December 2010 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
IMPRESSUM 
© DIW Berlin, 2010
DIW Berlin 
Deutsches Institut für Wirtschaftsforschung 
Mohrenstr. 58 
10117 Berlin 
Tel. +49 (30) 897 89-0 
Fax +49 (30) 897 89-200 
www.diw.de  
 
ISSN 1861-1532  
 
All rights reserved. 
Reproduction and distribution 
in any form, also in parts, 
requires the express written 
permission of DIW Berlin. 
  
 
 
Data Documentation  53 
 
 
 
 
John P. Haisken-DeNew * 
Markus H. Hahn ** 
 
 
PanelWhiz:  
Efficient Data Extraction of Complex Panel Data Sets –  
An Example Using the German SOEP    
 
 
 
 
 
 
Berlin, November 2010 
 
 
 
*  Ruhr University Bochum, RGS, RWI, IZA and DIW Berlin, Germany and McMaster University, 
Canada 
**  Melbourne Institute of Applied Economic and Social Research, The University of Melbourne, 
Australia Data Documentation  53 
Abstract 
II 
 
 
 
 
 
Abstract 
 
This paper outlines a panel data retrieval program written for Stata/SE 10 or 
better, which allows easier accessing of complex panel data sets. Using a 
drop-down menu and mouse click system, the researcher selects variables 
from any and all available years of a panel study. The data is automatically re-
trieved and merged to form a “long file”, which can be directly used by the 
Stata panel estimators. The system implements modular data cleaning pro-
grams called “plugins”. Yearly updates to the data retrievals can be made 
automatically. Projects can be stored in libraries allowing modular administra-
tion and appending. The paper exemplifies the power of PanelWhiz using the 
example of the German SOEP (German Socio-Economic Panel Study). 
 
Keywords:   Panel data, storage, retrieval 
JEL:     C81, C87, C23 
 
 
 
This paper appears in “Schmollers Jahrbuch”, Vol. 130, No. 4, 2010Data Documentation  53 
Verzeichnis der Tabellen 
Contents 
 
1  Introduction .........................................................................................................................1 
2  Overview of PanelWhiz.......................................................................................................3 
2.1  Getting Started with PanelWhiz..................................................................................... 3 
2.2  Some Details Using the German SOEP......................................................................... 4 
2.2.1  Items and Specials...............................................................................................6 
2.2.2  Item Browse Page................................................................................................7 
2.2.3  Plugins 11 
2.2.4  Retrievals...........................................................................................................12 
3  Summary............................................................................................................................14 
 
III Data Documentation  53 
1 Introduction 
 
1 Introduction 
Applied social scientists have forever been faced with different data interfaces for different 
data sets. In most cases, an interface is not even available, forcing the researcher to address 
data files by name, and extract the information required by hand. However, the specific struc-
ture of panel data can be very complex and vary dramatically as described in Haisken-DeNew 
(2001). Some panel data sets provide many files per year (“wide format”), differing by their 
population, or level of aggregation etc., creating many obstacles for researchers. If one wants 
to put together variables across time (“long format”), this is typically much more difficult, but 
ultimately the format which is required for estimation.  
PanelWhiz is a collection of subroutines that allows researchers to use an intuitive “common” 
graphical interface for accessing many panel datasets directly within the statistical package 
Stata/SE 10 or better (http://www.stata.com), whereby the researcher does not select individ-
ual variables, but rather vectors of variables (items) with one mouse click. This allows for an 
efficient method of selecting information for a data set retrieval, especially if the panel data 
set contains many waves (years) of information. With one mouse-click, data can be automati-
cally retrieved, with merging and matching done automatically. With the PanelWhiz system, 
the user can open data files by clicking on a browse page.  
The idea behind the tool is that because of the intrinsically longitudinal nature of the data, one 
is typically not interested in retrieving a variable in a single wave, but rather in retrieving the 
variable for several waves, i.e. an item. For all data sets, a variable renaming algorithm 
(where necessary) is used to ensure time consistent variable names (See Haisken-DeNew, 
2001 for more information on this). Thus, if one opens a data file and one finds a variable of 
interest, one clicks on the variable and information for the entire item (vector of variables) is 
also collected and added to a PanelWhiz “project”. Straightforwardly, the object is to collect 
items and save them into the data “project”, allowing an automatic data retrieval. The data are 
extracted in “long” format allowing easy further data cleaning or direct estimation using 
Stata’s panel “xt” commands. 
PanelWhiz, since appearing in 2006, now has several hundred registered users, using the 
common interface to access many different datasets, such as the German SOEP, British 
BHPS, the Australian HILDA, the German IAB Establishment Panel, the American CPS, etc.  
1 Data Documentation  53 
1 Introduction 
 
Recently, support has been extended to the American PSID. This paper describes using   
PanelWhiz for the German SOEP, providing specific examples for this panel data set. How-
ever, due to the generalized nature of PanelWhiz, the interface is almost completely identical 
for all other supported datasets and thus this paper can also be used as a general reference for 
other supported data sets. 
2 Data Documentation  53 
2 Overview of PanelWhiz 
 
2  Overview of PanelWhiz 
2.1  Getting Started with PanelWhiz 
PanelWhiz is described in detail on the PanelWhiz Website at http://www.panelwhiz.eu and 
has been presented several times at international data conferences, the UK Stata Users Group 
and the German Stata Users Group.  Due to the extensive size of PanelWhiz, the user down-
loads only a very small startup Stata Add-On program from the PanelWhiz Website. This 
small startup program then downloads automatically the component parts required for the full 
installation over the internet. All component parts are stored in compressed format, and as 
such, typically require only one-tenth of the usual download time and are automatically ex-
panded locally on the user’s hard disk.  
PanelWhiz is installed as a collection of Stata Add-On programs and loads every time Stata is 
started. For example in following Screen one can select by mouse click the desired data set to 
be supported.  
 
Screen Shot 1: Select Data Set 
 
3 Data Documentation  53 
2 Overview of PanelWhiz 
 
2.2  Some Details Using the German SOEP 
In the example (Screen Shot 2), the German SOEP has been selected. One can select an al-
ready existing project, or create a new one from scratch. In this example, we will examine an 
existing project zufr.soep. Because it has been already saved, PanelWhiz keeps a note of the 
last 10 saved projects and allows easily loading by simply clicking on the link indicating the 
project name. 
 
Screen Shot 2: Open a Project 
 
 
Here we have indeed opened the PanelWhiz project zufr.soep, and have a heads-up display 
indicating the contents of the project and the possible project commands as seen in Screen 
Shot 3. The top area displays the possible project commands and the bottom area the contents 
of the project. Here the project already contains 4 items (vectors of variables). The item labels 
are clickable, linked to a keyword thesaurus. 
 
4 Data Documentation  53 
2 Overview of PanelWhiz 
 
Screen Shot 3: Project Page 
 
The variables underlying the 4 items in the project are listed below (Screen Shot 4). Each 
variable listed under the item displays the associated wave/year. In the SOEP, the “a” wave is 
1984, the “b” wave is 1985 and so on. The “z” wave is 2009. Thus for the item IK622, in 
1984 the underlying variable is ap6801 and in 2009 it is zp15701.  
Screen Shot 4: Start Page 
 
5 Data Documentation  53 
2 Overview of PanelWhiz 
 
One can update the project using the automatic “Update” function on the project page. When a 
new data distribution becomes available, for each item, the newest variable is added automatically 
to the relevant item. The retrieval can be run again, having now the most recent information. 
 
2.2.1  Items and Specials 
Assuming that one would like to add any items to the project, one can choose between two 
types of concepts: “items” or “specials”. Items are vectors of variables that have a standard 
time dimension associated with them, i.e. one variable for each year. SOEP examples of these 
files would be ap.dta, apgen.dta, ah.dta, ahgen.dta etc. “Specials” have a non-standard 
time dimension, i.e. they may have one observation per person and be time invariant, or may 
already be in long format, with person-year observations as the unit of analysis.1 Here we will 
first examine the page associated with items.  
Clicking on a year like [a – 1984], will load a browse page allowing one to click on all 
variables/items associated with the year 1984. Alternatively, one can click on a special file, 
such as [ bioimmig ] where the data is already in long format. In contrast, the information 
from the special file [ bioparen ] is time invariant and contains only one entry per person. 
PanelWhiz knows how to extract and merge the information from all of these kinds of files 
(Screen Shot 5). 
                                                                          
1 To learn more about the long or wide data format, see http://www.stata.com/help.cgi?reshape 
6 Data Documentation  53 
2 Overview of PanelWhiz 
 
Screen Shot 5: Items and Specials 
 
 
For both items and specials, all associated items have been scanned and the contents of the 
item labels have been catalogued into a thesaurus of keywords. Thus, if one were interested in 
all items or specials regarding the topic of “occupation”, one would click on the [o] of the 
keyword index, to examine all keywords starting with the letter “o”. 
Technically speaking, PanelWhiz works because the item correspondence information (for 
each item, that vector of variables over all 26 years which are available currently for SOEP) is 
injected into each of the relevant variables as a Stata variable characteristic. PanelWhiz reads 
this information from a variable in one particular file/wave and automatically knows where to 
find the corresponding information in all other files/waves. 
 
2.2.2  Item Browse Page 
To find an item available say for the year 2009, we click on the year [z – 2009], and receive 
the following browse page (Screen Shot 6). The browse page contains all variables/items from 
all files in the year 2009. Alternatively, one can jump to only specific variables/items in a 
particular file. Further, the variables for the German SOEP are sorted using the same hierar-
7 Data Documentation  53 
2 Overview of PanelWhiz 
 
chical categorisation scheme as in SOEPinfo. See Haisken-DeNew and Frick (2005) for more 
information on SOEPinfo. 
 
Screen Shot 6: Top Level Item Browse Page 
 
 
In this example, we select the clickable button [ALL FILES] from wave “z” and get the fol-
lowing browse page (Screen Shot 7). All variables are listed in the order they naturally exist 
in the respective physical data files. The example shows that for the SOEP variable erwtyp09, 
there is a PanelWhiz item IK2264 associated with it. By actually clicking on IK2264, one 
would select the entire item (potentially all underlying variables from wave “a” through “z”).  
 
8 Data Documentation  53 
2 Overview of PanelWhiz 
 
Screen Shot 7: Look at Items 
 
 
One can also examine the changing nature of the item over time. The variable erwtyp09 con-
tains value labels. Thus we can click on “i” to the left of the item name and label. There is a 
ready-made HTML page showing all labelled values for all variables of the entire item. This 
will be especially useful information for data cleaning requirements. Just because a variable 
has been coded one way in one year/wave, it does not mean it will remain so over all time. 
Screen Shot 8 illustrates this example. Jumping from wave 1984 to 1985, there have been 
some additional outcome values added. These changes are colour coded in grey. 
 
Screen Shot 8: Start Page 
 
 
9 Data Documentation  53 
2 Overview of PanelWhiz 
 
By clicking on “N” to the left of the variable label, one can view the item “notes”, giving an 
indication of the variable names of variables belonging to the item over all years (Screen Shot 
9). 
Screen Shot 9: Item Notes 
 
 
All words appearing in an item label have been added to a keyword thesaurus. Each keyword 
is linked to all items in the entire dataset containing the keyword (see Screen Shot 10). 
Screen Shot 10: Keywords 
 
 
10 Data Documentation  53 
2 Overview of PanelWhiz 
 
2.2.3  Plugins 
The variables underlying an item may vary of course from year to year. Using the PanelWhiz 
plugin system, the user can automatically create small scripts to clean time inconsistent data 
(Screen Shot 11).   
 
Screen Shot 11: Plugin Wizard 
 
 
Depending on the dataset, various actions can be selected. For example (Screen Shot 12), 
using plugins, nominal money values can automatically be deflated using an integrated CPI 
function. 
 
11 Data Documentation  53 
2 Overview of PanelWhiz 
 
Screen Shot 12: Plugin Wizard and Defining Functions 
 
 
2.2.4  Retrievals 
Once a project has been created, PanelWhiz has enough information to retrieve the actual data 
from the panel dataset, in this case the German SOEP. PanelWhiz dynamically creates a DO 
command file for Stata and executes it on-the-fly. PanelWhiz opens the files that the user 
specifically addressed and pulls out the variables specifically selected. It stores these variables 
in a temporary file and then moves on to the next data file. It does this many times until all 
data chucks have been extracted, and then merges all data chunks together in the manner 
prescribed by the user. 
The extracted data are automatically in Stata long format, ready to be processed using Stata’s 
rich panel “xt” commands. To document exactly the retrieval that was run, PanelWhiz creates 
an executable DO file on the fly. Screen Shot 13 shows an excerpt of a generated DO file.  
 
12 Data Documentation  53 
2 Overview of PanelWhiz 
 
Screen Shot 13: Excerpt of a generated DO file 
 
13 Data Documentation  53 
3 Summary 
 
14 
3 Summary 
PanelWhiz is a data retrieval tool that simplifies data extractions from the many large scale 
data sets such as the German SOEP. PanelWhiz is directly combined into Stata/SE 10 or bet-
ter, allowing a seamless interaction between the micro data and the statistics package. Entire 
vectors of variables, called “items” can be selected at once. Special cleaning programs written 
in Stata called “plugins” can clean a particular item and make it time and/or content consis-
tent. 
All programs used are available in source Stata code which allows complete transparency of 
content. All commands used in the generated retrieval are documented in a fully functional 
retrieval DO file, capable of recreating the identical retrieval at any time. 
Groups of items can be stored as “projects”. Groups of projects can be stored as “libraries”. 
This method of organizing the projects and plugins allows for a modular administration, fa-
cilitating knowledge transfer and group work. Data can be retrieved by mouse-click, provid-
ing rectangularized “wide” data in “long format”.  As new releases of the panel data set be-
come available from the data provider, the user can “automatically” update his projects to 
include the latest wave of information. 
 
References 
Haisken-DeNew, John P. (2001) “A Hitchhiker's Guide to the World's Household Panel Data Sets.” 
The Australian Economic Review. 34(3), 356-366. 
Haisken-DeNew, John P. and Joachim R. Frick (2005) “The Desktop Companion to the German So-
cio-Economic Panel Study”, DIW Berlin, Germany 

A Hitchhiker's Guide to the World's Household Panel Data Sets.” The Australian Economic Review.

The Desktop Companion to the German Socio-Economic Panel Study”,

PanelWhiz - Efficient Data Extraction of Complex Panel Data Sets: An Example Using the German SOEP

http://www.diw.de/documents/publikationen/73/diw_01.c.364985.de/diw_datadoc_2010-053.pdf

PanelWhiz - Efficient Data Extraction of Complex Panel Data Sets: An Example Using the German SOEP

Abstract

Similar works

Full text

Available Versions

Research Papers in Economics