Cumulative sum by series - how do it quickly?

[adrian_d]

Hello,
in Eviews there are many functions to make cumulative sums and to make statsby, but I cannot solve the kind of "cumulative by" problem. The problem is to have a series with counted occurence of specific values of the other series, within each ID variable separately.
Please find attached the workfile, it is unstructured/undated workfile, and there:
- CONVERSION_ID is our variable, that we want to group by
- INTERACTION_CHANNEL is the variable with categories, that we want to calculate occurence of, within the Conversion IDs
- INTERACTION_DATE_TIME is the exact date of each instance of interaction channel - maybe it will be helpful to have it
- OCCURENCE is what we want to achieve - I fill in the values manually, to ilustrate you how it should look like.

Is it possible to automize it smartly in Eviews (v. 8.1) ?

thanks
adrian

[EViews Gareth]

Probably an easier way, but:

Code: Select all

vector ids = @uniquevals(conversion_id) svector chns = @uniquevals(interaction_channel) for !i=1 to @rows(ids) for !j=1 to @rows(chns) %chn = chns(!j) smpl if conversion_id = ids(!i) and interaction_channel=%chn series temp = 1 series occ = @cumsum(temp) delete temp next next smpl @all

[adrian_d]

Probably an easier way, but:

Code: Select all

vector ids = @uniquevals(conversion_id) svector chns = @uniquevals(interaction_channel) for !i=1 to @rows(ids) for !j=1 to @rows(chns) %chn = chns(!j) smpl if conversion_id = ids(!i) and interaction_channel=%chn series temp = 1 series occ = @cumsum(temp) delete temp next next smpl @all

Okay, it's quite similar to what I've tried, not too quick when I have thousands of conversion ids, but thanks. Maybe it would be good idea to take such a function into consideration in the upcoming Eviews updates.

[EViews Gareth]

EViews Glenn came up with a, possibly, faster solution:

Code: Select all

series id = @trend+1 pagestruct(create) conversion_id interaction_channel series occ=@trend+1 pagestruct(none) sort id

[EViews Glenn]

I think you need to recode at the end to handle the blanks in interaction_channel:

Code: Select all

occ = @recode(interaction_channel="", NA, occ)

[adrian_d]

Yeah, making this pagestruct and @trend is great, thank you very much guys!

[kche260532]

Hi EViews team,

Sorry to have a follow up question on a post 6 years ago. I got an exact issue that ardian_d came across. Gareth and Glenn's solutions both work well on the issue until more recently I got a big dataset that involves 3 million observations. Eviews couldn't do the "pagestruct" as the group variable consists of 1.2 million groups. That exceeds the limit of "pagestruct"; whereas the loop from Gareth will take me around 13 days to complete the looping. For the time being, I try R; and use dplyr mydata %>% group_by(group, obs) %>% mutate(myorder = 1:n()) to complete the task. But I would be grateful if I can have a solution to program that in Eviews, to save some hiccups in switching in between software.