Multiple Conditional Summing and exporting to Excel

[JimJohnson]

I have a series of databases of vehicles (2 million records each for each period 2000 quater 1 to 2011 quater 4) with roughly 10 characteristic fields (e.g., location, class, fuel, license type, fuel efficiency, etc.). Using the programming language, I want to sum all vehicles that are in location 1, class A, fuel i, then location 1, class A, fuel ii, etc. The structure needs to be:

Location Class Fuel License Type Count 2000 q1 Count for 2000 q2 ........... Count for 2011 q4
1 Compacts G W
1 Compacts G P
1 Compacts D W
1 Compacts D P
1 Full-Size G W

59 Compacts G W
etc.

The resulting structure, including the counts (or the average of efficencies), needs to be exported to Excel. Doing this in the programming language will enable me to do this automatically since I have at least a dozen summations or averages that I need.

Thoughts?

[EViews Gareth]

We'll need considerably more information. What structure is the data currently?

[JimJohnson]

The data are .dbf files, but I can easily put them into Eviews workfiles.

There is a separate database for each period, that is, one for 2000 q1, another for 2000 q2, etc.

Each database contains roughly two million records with, as said, about 10 or so fields with different characteristics.
Location: numeric field describing the city location
Class: each vehicle is assigned a class: subcompact, compact, mid-size, full-size, SUV, Pickup, Minivan, Medium Duty, etc.
Fuel: each vehicle is assigned a fuel: gasoline (G), Diesel (D), Hybrid (H), Elelctric (E), Other (O)
License Type: Work (W) or Pleasure (P)
Fuel Efficiency: each vehicle is assigned a fuel efficency
Sex of Principal Driver: numeric: 1 for male
Age of Driver: numeric: 1 for age 16-24, 2 for age 25 - 64, 3 for age 65+

There are a number of other fields as well.

Is that enough info?

[EViews Gareth]

Well before doing the summing etc..., the question I have is how you're going to get them into a Workfile. 2m observations per period, with 48 periods is far too many observations for EViews.

If you were theoretically able to get them into an EViews workfile, it seems that the @sumsby function do the actual summing.

[JimJohnson]

Could I not load one workfile, do the conditional summing for that workfile, save the resulting file, delete the workfile and load in the next file, all through the programming language?

[EViews Gareth]

Sure, if you want them in separate output files.

[gg123]

Not to take over the thread, but I have a similar issue.

My date is in Eviews as a dated panel, with year and quarter comprising the dateid, the cross section ID series consisting of 5 identifiers.

I used the @sumsby command to appropriately sum by the identifiers I wanted - newsum=@sumsby(count,id1 id2 id3) . The results seem to be accurate, but now I have quite a few duplicates - the same 'newsum' value is repeated each for each line that has the same id1-3 values, (id4-5 differ).

How do I remove the duplicate lines, or better still, put only the unique lines into a new page?

[EViews Gareth]

I can't picture what your newsum series contains. What are count, id1, id2, id3 etc..?

[gg123]

I would like the newsum series to contain a sum of the count variable.

count is a numeric value of vehicles that have a specific combination of characteristics.

id1 id2 id3 etc... are those characteristics (such as weight, model year etc.)

Basicially I want my vehicle counts in larger groups (say, by eliminating an identifier such as weight), then continue on with my analysis.

I think I figured it out though.... I created a new page, specified by identifier series, including only the cross id series that I am interested in, then copied and pasted my count series into the new page, using the sum contraction method.

I would prefer to do this in commands rather than using the windows, as I will be aggregating in different ways in the future. Is there some way to do this in commands?

Did you still require more information? If so, please let me know.

Thanks.

[EViews Esther]

I think I figured it out though.... I created a new page, specified by identifier series, including only the cross id series that I am interested in, then copied and pasted my count series into the new page, using the sum contraction method.

Although admitting that it is not clear to me what your question is exactly, I could introduce one way to implement your idea.

Code: Select all

pagecreate(id,page=new_page_name) id 'see Command ref on p302 about the method to handle ID values that differ across multiple pages copy(c=sum) your_current_page\count_series new_page_name\count_series

[gg123]

Yes, that is the solution I am looking for. Thank you for your help and direction, and sorry for not being so clear. I'm pretty new to this program, and am not quite used to how to get it to work for me in everything yet.