How to enter data into a panel workfile.

For requesting general information about EViews, sharing your own tips and tricks, and information on EViews training or guides.

Moderators: EViews Gareth, EViews Moderator

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

How to enter data into a panel workfile.

Postby EViews Gareth » Fri Oct 10, 2008 9:39 am

There are a number of different ways to enter data into a panel workfile. Which method works best really depends upon how your data starts out.

In this post I'm going to describe a method which converts data that is currently in "pool" form into a panel. For posts on entering data into a panel from Excel files, scroll down a bit! The workfile I'm using can be found at:

http://www.eviews.com/Forum_support/poo ... poolg7.wf1

The workfile looks like this:
Image

As you can see there are 7 cross-sections, CAN, FRA, GER, ITA, JPN, UK, USA, with 43 years of data (1950 to 1992) and two data series, GDP and Unemp.

Moving this data into a panel workfile is relatively simple. Via the menus you can do this:

Click on Proc->Reshape Current Page->Stack in New Page
Image

Enter the stacking identifiers.
You have a number of choices for this. The easiest, in this case, is to enter a series pattern. In our case our series (gdp and unemp) have a pattern of series_crossid. Thus I can use one of my series as a pattern. In this case I used unemp? The "?" represents the cross-section identifier (in this case _US or _CAN).
Image

Hit ok, and you're done!
Image

Note how you now have a panel workfile (you can tell from the Range statement at the top of the workfile - the fact it has a x7 at the end lets us know it is a panel).


Note if you wanted to do this via the command line, you just need to do this:

Code: Select all

pagestack unemp?


Simple!
Follow us on Twitter @IHSEViews

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Fri Oct 10, 2008 10:50 am

If your data is currently in Excel and you want to put it into a panel workfile, then, again, the best way to do it really depends upon what form the data is in inside Excel.

However here is one example.

A common form of panel data structure in Excel is the following:
Image

Note you can get this Excel file here.

Reading this sort of file into an EViews panel is easy. First open the file directly into EViews:

File->Open->Foreign Data as Workfile

Image

Select the Excel file and hit Open

Image

Check everything looks ok on the preview, and hit Finish

Image

If you're using EViews 7, you're now done (since EViews 7 will automatically recognise this data as a panel)!

If you're using EViews 6 you will now have an unstructured workfile with all of the data:
Image
And all that's left is to structure it as a panel:
Hit Proc->Structure/Resize Current Page
Change the Workfile structure to "Dated Panel"
Enter Country as Cross section ID
Enter Year as Date series


Image

And you're done!

Image

Note all of that could be done via the command line with the following:

Code: Select all

wfopen http://www.eviews.com/Forum_support/Excel2panel/byyear.xls
pagestruct country @date(year)

(note the last line is not required in EViews 7)
Follow us on Twitter @IHSEViews

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Mon Feb 02, 2009 1:12 pm

A second type of format that is used in Excel, is where the columns represent dates, then the rows represent both cross-sections and variables. Such is the case in the following:
Image

This file can be obtain from here.

This sort of structure is a little more tricky, and takes a bit more effort inside EViews. If you bring the file in by opening it into EViews, you will get a workfile that looks like this:
Image

The first thing we need to do is to stack this into a new page:
Image
Image
Notice that we use yr? as the stacking identifier since the series in EViews are all named yrXXXX.

The stack results in the following workfile page:
Image

Now we need to unstack this page into a panel. First, though, we need to change our "Variable" series a little. This is because the text inside that variable contains spaces, and we want to create new series from this text, but series names cannot contain spaces. Thus we type the following in the command pane to create a new alpha series containing valid names:

Code: Select all

alpha var2=@replace(variable," ","_")
show var2
d variable


Image

Once this new alpha series has been created, we can do the unstack:

Image
Image
Image

Now we're pretty much done. We just need to type a few more commands to tidy things up a bit (delete some un-needed series and rename some):

Code: Select all

d yr
rename yr* *

Image

And we're done!
Follow us on Twitter @IHSEViews

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Fri Jan 08, 2010 3:04 pm

A third Excel data format is where each cross-section has its own sheet inside the Excel file, such as the one below (note each sheet represents a different country's data):
Image

Note the Excel file can be downloaded from here.

Currently EViews does not support any easy way to bring such a file into EViews. However a relatively simple EViews 7 program can be used to do the job. Such a program is outlined below. Note this program assumes (and requires) that the names of the sheets in Excel correspond to the names of the cross-sections:

Code: Select all

%filename = "c:\temp\sheetpanel.xlsx"   'file name of the file to be opened

%sheetnames = @tablenames(%filename)   'find the names of the sheets in that file

%sheetname = @word(%sheetnames,1)   'get the first sheet name

wfopen(wf=panel) %filename range=%sheetname    'open the first sheet as a new workfile (with name=panel, and pagename=the first sheet name)

alpha crossid = %sheetname   'create a cross-section identifier series, and set it equal to the sheetname

'loop through the remaining sheets, loading them into the workfile one at a time
for !i=2 to @wcount(%sheetnames)
   %sheetname = @word(%sheetnames,!i)  'get the name of the next sheet
   import %filename range=%sheetname @append  @genr crossid=%sheetname   'append the next sheet to the bottom of our workfile
next

pagestruct @date(year) crossid   'structure the page as a panel, by stating that the series "YEAR" is a date id and the series "CROSSID" is a cross-section identifier.


Note the first line of the program specifies the name of the Excel file. You should change that line to match the location of your own Excel file. The program first of all finds the names of the sheets in the Excel file, then opens up the first sheet as a new EViews workfile. It then loops through the remaining sheets, one at a time, appending them to the created workfile. Each time a sheet is brought into EViews, the alpha-series "country" is set equal to the name of the sheet, thus building up a cross-section identifier series.

Once all the sheets have been brought in, the pagestruct command is used to structure the page as a panel:
Image
Follow us on Twitter @IHSEViews

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Mon Feb 07, 2011 5:07 pm

We've recently had a customer who had another interesting type of panel data format. This Excel file has a quarterly for a number of different years in each tab. For example, the first tab, called "2002q3" has quarter 3 data for 4 different products over the years 1998-2002. The next tab has quarter 2 data for the same widgets between 2000 and 2002. What makes this file even more interesting is that as you move through the tabs, data can be updated. For example the 5th tab has quarter 3 data for 1998-2003. This data super-cedes the data that was given in the first tab.

The Excel file is available for download here. The first two tabs look like this:
Image
Image

As with the previous example, you'll need to use an EViews 7 program to bring this into EViews nicely. Such a program is outlined below:

Code: Select all

%filename = "c:\temp\salespanel.xls"   'file name of the file to be opened

%sheetnames = @tablenames(%filename)   'find the names of the sheets in that file

!numsheets = @wcount(%sheetnames)

%sheetname = @word(%sheetnames,!numsheets)   'get the last sheet name (which is the first chronologically)

wfopen(wf=panel) %filename range=%sheetname  @freq q  'open the last sheet as a new workfile (with name=panel).  Note the use of @freq q to insist that the data coming in is quarterly.

for !i=!numsheets to 1 step -1    'loop through the sheets, starting at the penultimate and working forwards
   %sheetname = @word(%sheetnames, !i)
   import(resize, mode=ms) %filename range=%sheetname @id @date(date) product @destid @date(date) product @freq q     'note the resize option to tell EViews to resize the page for the new data, and the mode=ms option to tell EViews to update the existing data whenever new data is available.  Also notice the explicit @id and @destid useage to tell EViews the exact nature of the data coming in, where dates are given by the "date" series, and cross-sections are given by the "product" series
next


Note the first line of the program specifies the name of the Excel file. You should change that line to match the location of your own Excel file. The program first finds the names of the sheets in the Excel file, then opens up the last sheet, which is the first sheet chronologically (2002Q3), as a new EViews workfile. It then loops through the remaining sheets, one at a time, appending them to the created workfile. Each time a sheet is brought into EViews, the workfile is extended to take into account the new dates being brought in, and any new data that has the same date as existing data will replace the existing data.
Follow us on Twitter @IHSEViews

Ydwolf
Posts: 14
Joined: Thu Oct 06, 2011 11:42 pm

Re: How to enter data into a panel workfile.

Postby Ydwolf » Wed Nov 02, 2011 3:06 am

My excel file is almost identical to your second example, Gareth. (pls see excel file attached)

Country Variable year2002 year2003 year2004 year2005

country1 trade
country2 ...
... imp
... ...
... exp

But when repeat the same steps as yours, it doesn't seem to work.
After the first step "stack in new page", i get following result(pls see printscreen attached). What should be my next step?
Once I get my data into eviews, the objective is to get descriptive statistics of the proxy variables trade, imp and exp.
Can you please guide me through it?
Tnx Gareth
Attachments
test_trade_imp_exp.xlsx
(23.77 KiB) Downloaded 1588 times
prtnscrn.png
printscreen
prtnscrn.png (15.34 KiB) Viewed 297122 times

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Wed Nov 02, 2011 8:08 am

From there click on Proc->Reshape Current Page->Unstack in New Page.

Enter "Variable" in the first box. Enter "Country Var01" in the second box. Hit OK.

That will get you to an undated panel. You can then click on Proc->Structure/Resize Current Page. Change the structure type to Dated Panel. Enter "Country" in the Cross ID box, and "Var01" in the Date Series box.

Then you just have to rename a few things to make it look pretty.
Follow us on Twitter @IHSEViews

thomasgilboy
Posts: 3
Joined: Thu Dec 15, 2011 8:06 am

Re: How to enter data into a panel workfile.

Postby thomasgilboy » Thu Dec 15, 2011 8:14 am

Hey Gareth,

I have data in the format of the third method but the code doesn't seem to be working for me, specifically when I change the filename to that of my dataset. I understand this may be a problem with my system/filename but I was wondering if you had any idea if it could be something else?

Thanks,

Tom

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Thu Dec 15, 2011 8:43 am

Hard to say without seeing your file.
Follow us on Twitter @IHSEViews

thomasgilboy
Posts: 3
Joined: Thu Dec 15, 2011 8:06 am

Re: How to enter data into a panel workfile.

Postby thomasgilboy » Thu Dec 15, 2011 8:55 am

Apologies, here is my file:

I've had to cut it down in order to fit as an attachment to this post.
Attachments
eviewsforum.xls
(1.66 MiB) Downloaded 1629 times

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Thu Dec 15, 2011 9:04 am

The problem is probably the last few sheets of your Excel file which don't match the pattern of the others - i.e. they're not sheets for a single country etc...
Follow us on Twitter @IHSEViews

thomasgilboy
Posts: 3
Joined: Thu Dec 15, 2011 8:06 am

Re: How to enter data into a panel workfile.

Postby thomasgilboy » Thu Dec 15, 2011 9:10 am

Ah sorry, this is the old version. The new version has those sheets removed and just the country sheets left in. I believe it's a problem with Eviews trying to find the Excel file. Not entirely sure how to fix that.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Thu Dec 15, 2011 9:11 am

I removed those sheets from the end, and saved the file as c:\temp\eviewsforum.xls

I then ran the following:

Code: Select all

%filename = "c:\temp\eviewsforum.xls"   'file name of the file to be opened

%sheetnames = @tablenames(%filename)   'find the names of the sheets in that file

%sheetname = @word(%sheetnames,1)   'get the first sheet name

wfopen(wf=panel) %filename range=%sheetname    'open the first sheet as a new workfile (with name=panel, and pagename=the first sheet name)

alpha crossid = %sheetname   'create a cross-section identifier series, and set it equal to the sheetname

'loop through the remaining sheets, loading them into the workfile one at a time
for !i=2 to @wcount(%sheetnames)
   %sheetname = @word(%sheetnames,!i)  'get the name of the next sheet
   import %filename range=%sheetname @append  @genr crossid=%sheetname   'append the next sheet to the bottom of our workfile
next

pagestruct @date(year) crossid   'structure the page as a panel, by stating that the series "YEAR" is a date id and the series "CROSSID" is a cross-section identifier.


and it worked perfectly.
Follow us on Twitter @IHSEViews

KLau
Posts: 5
Joined: Wed Apr 11, 2012 5:20 am

Re: How to enter data into a panel workfile.

Postby KLau » Wed Apr 11, 2012 5:32 am

I am trying to convert my data (printscreen2) into panel data.
The problem seems to be however that if I want to transform the data through the normal procedure (Proc - Structure/ Resize page), Eviews does not correctly recognize the years.
This leads to the question in 'printscreen' and this leads to the fact that my data aren't properly converted into panel data.
The structure of my years always is as following:
Region 1:
2003
2003
...
2004
2004
...
2005
...
Region2:
2003
2003
...
As you can see in 'printscreen2'.
Could you please help me with solving this problem?

Thanks in advance.
Attachments
prtscrn2.JPG
PrintScreen2
prtscrn2.JPG (367.9 KiB) Viewed 290883 times
prtscrn.JPG
PrintScreen
prtscrn.JPG (78.98 KiB) Viewed 290883 times

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: How to enter data into a panel workfile.

Postby EViews Gareth » Wed Apr 11, 2012 7:47 am

Could you post the data?
Follow us on Twitter @IHSEViews


Return to “General Information and Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 12 guests