Not applicable
Posts: 1

# Calculate

how to calculate mean

Super Contributor
Posts: 418

## Re: Calculate Average of Statistic Before and After Event

I may be reading this incorrectly but I think your request has a logical flaw.  Read your bullet point 2 and 3. You are saying the same logic flags both indicator and indicator_prev, so they would logically do the same thing.

Are you trying to figure out the 3 year average before the event, and the three year average after the event, where if the events share years it's considered a 'before' category? That's what i'm understanding from your question, let me know if I'm off track.

Super Contributor
Posts: 418

## Re: Calculate Average of Statistic Before and After Event

Haha okay I was thinking there was something you weren't saying that made it more of a challenge!

SO the next logical question is. What happens if events occur within 3 years of eachother? Then a 3 year lag doesn't make sense...  Would the 'last 3 years look back' only be the number of available years since the last event?

So the lookforward and lookback are the maximum between (number of years between events,3)?

I would start by making a year spine that joins between the minimum and maximum value of the actual years given -3(+3), and the minimum event -3(maximum +3)  per each of your event codes.  For example, for company #3 it would be 1998-3 (since that's your minimum event -3). and it would go all the way to 2005 (max of years and events for 003 is '2002', and +3= 2005).

Once you have that, left join both your events table, and your year spine table onto the spine.  Then you can use retain statements to retain the last event down row by row until it reaches the next event. Grab a counter of the number of retains you had to make (that's your 'years since').   Do the same thing on a reverse sort order, and that's your 'years before'.  Then you have all of the columns necessary to do summary information by event, by any "x" year window.

Let me know if that doesn't make sense at any level.

Super User
Posts: 23,700

## Re: Calculate

Deleting a question is considered bad form.  If its incorrect, edit it but don't delete it, its a waste of other users time.

Contributor
Posts: 54

## Re: Calculate

Perhaps it was a homework question?  This is Google's cache of what mmm3902 originally posted:

----------------------------------------------------------------------------------------------------------------

Data1:

• Company_Code
• Event
• Event_Year

Data2:

• Company_Code
• Year
• Statistic

Data1

 Company_Code Event Event_Year 001 1 2000 002 0 . 003 1 1998 004 0 .

Data 2

 Company_Code Year Statistic 001 1999 60 001 2000 55 001 2002 60 001 2003 36 001 2004 12 001 2005 12 003 1999 60 003 2000 60 003 2002 55 004 2000 36 004 2005 60

I need to program where for each Company_code in Dataset1; I look for the same Company_code in Dataset2.The following should all go into Dataset1:

• Compare Event_year (Dataset1) to Year (Dataset2).
• If Event_year (Dataset1) > Year (Dataset2); INDICATOR =1; else INDICATOR=0;
• If Event_year(Dataset1) > Year (Dataset2); INDICATOR_PREV =1; else INDICATOR_PREV=0;
• For INDICATOR =1, calculate SUM(Statistic)
• For INDICATOR_PREV, calculate SUM(Statistic)
• For 3 years prior to each event for each company in Dataset 1, calculate corresponding average of STATISTIC.
• For 3 years after each event in Dataset for each company in Dataset 1, calculate corresponding average of STATISTIC.

Any help on this would be appreciated! I've taken on a bit more than I could handle right now and am hoping for some guidance.

Thanks!

Discussion stats
• 4 replies
• 278 views
• 0 likes
• 4 in conversation