Identify and delete duplicate observations.

[rdwest]

This ought to be very easy, but I can't seem to find a way to count the number of distinct observations over a set of criteria. There ought to be a by-group function that does this, right? This is quite simple to do in SAS, SQL, or Stata. I think the question's been posed on this forum before, but I can't find a thread that's been responded to.

My exact problem is that I've got several duplicate observations in a workfile out of which I want to create a panel dataset. I have a cross-section id, and have created a date id. I should be able to create a series that counts the number of distinct observations in each cross-section/date group, and delete any observations that have a count of more than 1.

(Well, it doesn't seem to be possible to actually delete observations from code.....but that's a separate issue in Eviews. I'll use the work-around with pagecontract that's been referenced before on this forum.)

Any advice?

Thanks,
R.


Update: It looks like the last couple of posts in this thread raised the same question -- but the moderators don't seem to have given any resolution. http://forums.eviews.com/viewtopic.php?f=3&t=2004

Or am I missing some follow-on?

I'm working with a very large dataset, so the work-around suggested (creating a new variable that combines the two variables of interest [i.e., in some way combining the cross-section and date ids to create a unique identifier] and then looping to sample for each value of the variable and counting the number of observations in the sample) would be very inefficient.

[EViews Glenn]

I'm not sure where the original posting is so I don't know why this wasn't answered (and this one just turned up in my RSS feed today :( )

This is pretty straightforward. How we do the final bit depends on exactly what you want to do the contraction.

First, to get the number of observations for each pair, simply use the @obsby series function. Suppose that you have ID as your cross-section ID series and DATE as your date series. Then

Code: Select all

series count = @obsby(1, ID, DATE)

should do it. Then you can do whatever you want with that variable. Since I'm not certain whether you want to just remove those obs in the contract, or whether you want to do something more sophisticated, I'll stop here, but if you have other questions, post to this thread and we'll try to answer more quickly.

[tzutzu]

hi all,

i am not sure if i should start a new post but it is closely related to the post. i am working on some account level data, my question is if i use @obsby to identify the duplicates then, how do I aggregate all the duplicates to form a unique series ?

thanks,

T

[EViews Gareth]

That's just a frequency conversion (via general match-merge). Just choose "sum" as the contraction method.

[tzutzu]

hi Gareth,

i cannot seem to create a list of account numbers without the duplicates. are you aware of any eviews comment that can help ? the series is stored as numeric.

Thanks,

T

[EViews Gareth]

Use the @uniquevals function.

[tzutzu]

hi Gareth,

is the function available in eviews 7.2 ? eviews seems to be telling me @uniquevals is an illegal or reserved name.

Cheers,

T

[EViews Gareth]

No, only in EViews 8.

You'll have to do a one way tabulation in EViews 7, then copy and paste.