Stacking raw data - help

[Chthoniid]

Hi all- I'm on Eviews 9 for the first time.

I'm trying to import data in a very irregular format, and shape it into something I can work with. It's not working.

The data records the number of butterflies caught by each collector in each village in PNG.
E.g.
Date YR Collector Town Q P TR
26 August 1993 1993 KEREU LAWAT Lorengau 15 2.5 37.5
26 August 1993 1993 KEREU LAWAT Lorengau 24 1.5 36
26 August 1993 1993 KEREU LAWAT Lorengau 12 0.2 2.4
26 August 1993 1993 KEREU LAWAT Lorengau 6 0.2 1.2
24 February 1993 1993 Michael Ndrih Lorengau 15 1.5 22.5
24 February 1993 1993 Michael Ndrih Lorengau 1 2 2
24 February 1993 1993 Michael Ndrih Lorengau 19 3 57
24 February 1993 1993 Michael Ndrih Lorengau 6 0.2 1.2
24 February 1993 1993 Michael Ndrih Lorengau 4 2 8
24 February 1993 1993 Michael Ndrih Lorengau 5 1.5 7.5
24 February 1993 1993 Michael Ndrih Lorengau 8 0.2 1.6
26 August 1993 1993 N'DRINEI HAPKAS Lorengau 9 1.5 13.5
26 August 1993 1993 N'DRINEI HAPKAS Lorengau 10 2.5 25
26 August 1993 1993 N'DRINEI HAPKAS Lorengau 3 0.2 0.6
26 August 1993 1993 N'DRINEI HAPKAS Lorengau 12 0.2 2.4
24 February 1993 1993 Paul Muguas Lorengau 12 3 36
24 February 1993 1993 Paul Muguas Lorengau 7 1.5 10.5
24 February 1993 1993 Paul Muguas Lorengau 7 0.2 1.4

What I want to end up with, is a workfile that aggregates each collector by month (not day) and sums the Q and TR values. The dates are irregular. And the number of records for each collector also varies.

I hope this makes sense and the process is not too complex.

TIA

B

[EViews Gareth]

Should be pretty straight forward, but we'd need the actual file.

[Chthoniid]

Thanks- I suspect there's something very simple I've overlooked.

File attached

[EViews Gareth]

Code: Select all

wfopen priamus_v2.xlsx svector collectornames = @uniquevals(collector) !numcols = @rows(collectornames) %firstmonth = @datestr(@min(date), "YYYY/MM/DD") %lastmonth = @datestr(@max(date), "YYYY/MM/DD") pagecreate(page=sum) m {%firstmonth} {%lastmonth} !numcols copy priamus_v2\collectornames sum\colnames alpha collector for !i=1 to !numcols smpl if @crossid=!i %name = colnames(!i) collector = %name next copy(c=s) priamus_v2\q sum\q @src date collector @dest @date collector copy(c=s) priamus_v2\tr sum\tr @src date collector @dest @date collector pagestruct @date(dateid) collector pagecontract if q<>na or tr<>na show q tr