Importing Panel data from Excel

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Jason, EViews Moderator, EViews Gareth, EViews Steve, EViews Pamela

Importing Panel data from Excel

Postby Jr_prodigy on Sun Apr 04, 2010 4:19 pm

Hey guys,

a quick question,I'm busy loading panel data into Eviews. I followed the guide over here: viewtopic.php?f=7&t=74 (the one with usa unemployment etc) but still managed to stumble on some problems. I work with Eviews 5.1 (university licenses only allow us to have 5.1 on personal laptops). When I add my data following the tutorial everything's fine uptill the reshaping. When I reshape and enter my time and cross section variables (which are recdats and ID) I get this error: http://i43.tinypic.com/j9rgc7.jpg

My data looks as follows: http://i44.tinypic.com/n4e5vt.jpg

NOF will be my independent variable on which I have to do OLS (mostl ikely regression), all the other variables are dependent except for recdats, no, ID.
Recdats is the time indicator (i can only import a year variable instead of recdats but the problem remains and gets even worse with about 30 duplications). No is actually not needed (still have to remove it) and ID should be my unique cross section identifier.

My data consists of the recommendation of financial analysts covering different firms, I made ID as an unique link between an analyst and a company. For instance analyst A with company Coca Cola has ID 1 throughout time and analyst A with company pepsi has ID 2, analyst B with coca cola has ID 3 and so on. I have a total of 1784 observations and 1688 ID's (which is logical since some companies-analyst duo's return over time). I deleted all extra information I deemed redundant such as analyst unique codes or firm unique codes (which I used to create the ID variable) also ticker etc. If you think I deleted necessary data do tell and ask if I have that kind of information..

What am I doing wrong that I get the error? When following the guide I enter recdats as date series and ID as cross section identifier.

Thank you, if something's not clear just ask.
Jr_prodigy
 
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby EViews Gareth on Sun Apr 04, 2010 5:45 pm

Well written question, thanks.

Sounds like you have some (6) cases where you have the same ID and date value for two observations.

Without seeing the actual Excel file, can't really say where they are.
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7725
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy on Mon Apr 05, 2010 1:08 am

Ok, euhm my data is the following Excel file (manipulated and stuff in SAS, for instance the ID I did with )

Code: Select all
proc sort data=code out=temp(keep = amaskcd ticker) NODUPKEY ;
by amaskcd ticker;
run;
data temp;
set temp;
ID=_N_;
run;


It's this file Try,
try.xls
(384 KiB) Downloaded 136 times
, now you said that I have duplicated observations where both time and ID are the same, so I was thinking if I give each a seperate id (and thus no unique analyst firm id) I end up with 1784 ID's so there can't be any duplication. However when I do this (this is the file Try2
try.xls
(384 KiB) Downloaded 136 times
) and apply the same methods I get a "Illegal Panel Structure RECDATS is nested within ID" error. So this is no solution either, what do you recommend? Find the 6 duplicated observations?
Attachments
try2.xls
(384 KiB) Downloaded 68 times
Jr_prodigy
 
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy on Mon Apr 05, 2010 2:33 am

Don't mind that previous data, it seems there went something wrong with the ID function in SAS itself, you'll notice that there were ID's of 1760 which is impossible as I only had 1688 different ID's, I retried everything and now ended up with ID's that look to be normal but still have 12 duplicated values.

Do I pick these out seperately or ?

This should be the correct data
finaltry.xls
(384 KiB) Downloaded 144 times
.

edit: Ok so I finally managed to clean everything up correctly, I now only had 2 duplicated observations, which I deleted seperately.

I loaded it in using the panel data guide, one question: it's normal that eviews automatically fills in the time gaps with NA data right? This does not influence furter regressions and stuff?
Jr_prodigy
 
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby EViews Gareth on Mon Apr 05, 2010 7:37 am

Glad you got it all sorted.

The balancing NAs won't affect regression at all. If you don't want EViews to put them in there though, you can uncheck the "Balance between starts and ends" check box on the page structure dialog.
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7725
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy on Mon Apr 05, 2010 7:46 am

Ah cheers man, that does work!

It's really annoying how Statistical Programs can make your day fly by because of errors ( no offense to Eviews though haha :P )
Jr_prodigy
 
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby sue on Wed Apr 07, 2010 2:56 am

Hi,
I have just followed the instructions how to import the panel data. However, I constantly get the Binary Read. :( How do I overcome this? I have attached the data.
Thanks.
Attachments
DATA TOGETHER 2.xlsx
(49.61 KiB) Downloaded 137 times
sue
 
Posts: 1
Joined: Tue Apr 06, 2010 4:53 am

Re: Importing Panel data from Excel

Postby EViews Gareth on Wed Apr 07, 2010 7:35 am

Which version of EViews?

If you're using EViews 6, then the problem is that EViews 6 doesn't read .XLSX files.
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7725
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Panel data from Excel

Postby tariqaziz on Tue Jun 10, 2014 8:09 am

Hey Guys
I am trying to open a panel data but its showing error by saying "NAs found in ID series". Although other variables contain NAs but the ID series contains the company names. I am wondering what possibly is going wrong. I am using Eviews 8.
tariqaziz
 
Posts: 5
Joined: Fri Sep 27, 2013 4:26 am

Re: Importing Panel data from Excel

Postby EViews Gareth on Tue Jun 10, 2014 8:26 am

Sounds like the series containing IDs (company names) contains NA values.
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7725
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Panel data from Excel

Postby tariqaziz on Tue Jun 10, 2014 8:49 am

I re-checked the data and found that in the column of date one cell was empty so when i entered the date and again reopened the file it was done. BTW thanks Gareth
tariqaziz
 
Posts: 5
Joined: Fri Sep 27, 2013 4:26 am

Re: Importing Panel data from Excel

Postby lmarcone on Tue Jul 15, 2014 3:18 am

Hello everyone,

I am currently having a problem. I created an excel spreadsheet with the intent to create a panel data. When I try to import the file to EViews the program does not automatically detect the panel data. When I try to make it panel data by selecting the options, the program tells me that he has too many same variables in the cross section ID series. Exactly he says "Repeated values found in source id series" How should I proceed to make it a panel data then? I am attaching my excel spreadsheet to the post in the scope it might help the cause.

Thank you
Attachments
largediss.xlsx
(339.2 KiB) Downloaded 13 times
lmarcone
 
Posts: 1
Joined: Tue Jul 15, 2014 3:06 am

Re: Importing Panel data from Excel

Postby EViews Gareth on Tue Jul 15, 2014 8:06 am

Some of your IDs have multiple observations with the same time period. For example there are two 25458/2003 values.
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7725
Joined: Tue Sep 16, 2008 5:38 pm


Return to Data Manipulation

Who is online

Users browsing this forum: Yahoo [Bot] and 2 guests