turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Calculate

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2014 01:47 PM

how to calculate mean

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mmm3902

09-30-2014 04:43 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mmm3902

09-30-2014 05:24 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mmm3902

10-01-2014 03:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

10-02-2014 12:33 AM

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!