Structuring a workfile with non-unique values

[DBW]

Hi there. I’m looking for some help with converting a large panel of data that has non unique date identifies into a set of timeseries.

I can’t post the datafile right now so I’ll describe it as best I can. I have a set of observations, each belongs to a category, has a date attached to it, and a transaction value. I want to create a timeseries of transaction values for each category. All this would be straightforward except that some categories might have more than one transaction on any given date, so I need to sum across those observations.

My second problem is that it’s a really large workflow. Something like 180 categories and 450,000 observations, so I’d prefer not to loop through the observations if I can avoid doing so - particularly as I will have to update the data quite frequently.

Are there any solutions to this, or am I looking at some kind of loop using @uniquevals? Thanks in advance for any advice.

[EViews Gareth]

I think you'll need to post an example file

[DBW]

Ok, I've attached part of the data file. Hopefully you can see what I mean now.

I think I could use pageunstack if the dates contained in 'record_dates' were unique to each of the transaction types contained in the alpha series called 'seriesname'. But they're not, so I can't.

Any thoughts?

[EViews Gareth]

Something like:

Code: Select all

pagecreate(id, page=page2) seriesname @date(record_date) copy(c=sum) page1\transaction_today_amt page2\* @src record_date seriesname @dest record_date seriesname pageunstack(page=page3) seriesname record_date @ transaction_today_amt

The first line creates a new page with the unique combinations of seriesname and date. The second line copies data from the first page to the second, summing values if there are duplicates. The third unstacks.

[DBW]

Thanks Gareth, this has worked perfectly. Really appreciate the help!