Importing Panel data from Excel

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

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

Jr_prodigy
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Importing Panel data from Excel

Postby Jr_prodigy » 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.

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

Re: Importing Panel data from Excel

Postby EViews Gareth » 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

Jr_prodigy
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy » 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 526 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 526 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 408 times

Jr_prodigy
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy » 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 521 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?

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

Re: Importing Panel data from Excel

Postby EViews Gareth » 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

Jr_prodigy
Posts: 4
Joined: Sun Apr 04, 2010 3:31 pm

Re: Importing Panel data from Excel

Postby Jr_prodigy » 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 )

sue
Posts: 1
Joined: Tue Apr 06, 2010 4:53 am

Re: Importing Panel data from Excel

Postby sue » 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 489 times

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

Re: Importing Panel data from Excel

Postby EViews Gareth » 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

tariqaziz
Posts: 5
Joined: Fri Sep 27, 2013 4:26 am

Re: Importing Panel data from Excel

Postby tariqaziz » 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.

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

Re: Importing Panel data from Excel

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

Sounds like the series containing IDs (company names) contains NA values.
Follow us on Twitter @IHSEViews

tariqaziz
Posts: 5
Joined: Fri Sep 27, 2013 4:26 am

Re: Importing Panel data from Excel

Postby tariqaziz » 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

lmarcone
Posts: 1
Joined: Tue Jul 15, 2014 3:06 am

Re: Importing Panel data from Excel

Postby lmarcone » 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 371 times

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

Re: Importing Panel data from Excel

Postby EViews Gareth » 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

Maikel4
Posts: 1
Joined: Mon Jan 12, 2015 4:55 pm

Re: Importing Panel data from Excel

Postby Maikel4 » Mon Jan 12, 2015 5:10 pm

Hi Gareth,

I've a question regarding the time range of my panel data. I have data points from 3/09/2005 (first observation) till 7/13/2014 (last observation). There are a lot of time gaps so I inserted the option to add "obs (NA) to remove date gaps so date follows regular daily frequency". However I want to set my timeframe from 1/01/2005 till 7/31/2014. Is it possible to also include NA's before my first observation and after my last observation? I tried to change the time range via "structure/resize current page..." by adjusting the start and end date but this didn't change the time range at all (range 3/09/2005 till 7/13/2014 kept unchanged). I added my workfile to the attachment.

Best,
Maikel
Attachments
test panel.wf1
(1.41 MiB) Downloaded 346 times

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

Re: Importing Panel data from Excel

Postby EViews Gareth » Mon Jan 12, 2015 9:52 pm

Make sure you have both the Pad options set.
Follow us on Twitter @IHSEViews


Return to “Data Manipulation”

Who is online

Users browsing this forum: Google [Bot] and 23 guests