Counting observations per observation per month.

JR_Prodigy — Mon, 05 Apr 2010 21:11:17 GMT

Hey guys, never thought I would have to make use of the "help me" board but guess everyone needs help now and then. My problem is the following:

I have data of the following sort:

Ticker _ Date Fem Analyst (dummy 1 if true) __ Variables of that month like beta

AA _ 01/04/2001 1 __ 0.61

AA _ 05/04/2001 1 __ 0.62

AA _ 08/04/2001 1 __ 0.63

AA _ 01/05/2002 1 __ 0.7

AA _ 04/05/2002 1 __ 0.71

AA _ 08/07/2002 0 __ 0.8

AA _ 07/04/2003 1 __ 0.4

and so on.. What I want to receive is the following:

Ticker _ Date Number of fem analyst Number of Male Analysts Total __Variables

AA _ 04/2001 3 0 3 __ 0.63

AA _ 05/2002 2 0 2 __ 0.71

AA _ 07/2002 0 1 1 __ 0.8

AA _ 04/2003 1 0 1 __ 0.4

So a counting algorithm that allows me to count the number of female and male analyst for a certain company per month( using dummy variable gender 0 or 1) and deletes all observations for that month except the most recent one (for instance for 08/04/01 this becomes 04/01 with 0.63 which is the most recent observation for beta for 04/01 for company AA) The example explains it all I guess?

Any ideas?

Re: Counting observations per observation per month.

sbb — Sat, 24 Apr 2010 15:26:37 GMT

Suggest you look at using a DATA step to create your "count of analyst" (each gender - using IF THEN ; ) based on some input data condition. Also, derive a PERIOD_START_DATE using the INTNX function and your "date" variable along with a suitable output format such as YYMM7 or otherwise. Use PROC SORT to order your data making use of DESCENDING option in your BY statement. Lastly use PROC SUMMARY to accumulate your count/quantlty variables to generate your summarized results output.

Scott Barry
SBBWorks, Inc.

topic Re: Counting observations per observation per month. in Statistical Procedures

Counting observations per observation per month.

Re: Counting observations per observation per month.