Help using Base SAS procedures

Calculate

Reply
N/A
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: 17,840

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 dkb
Contributor
Posts: 53

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_CodeEventEvent_Year
00112000
0020.
00311998
0040.

Data 2

Company_CodeYearStatistic
001199960
001200055
001200260
001200336
001200412
001200512
003199960
003200060
003200255
004200036
004200560

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!

Ask a Question
Discussion stats
  • 4 replies
  • 257 views
  • 0 likes
  • 4 in conversation