How to count the number of observation over the 10 years prior to certain month

[Neal3321]

I attached a sample for the dataset. The sample include two variables: personid and ym. Personid refer to the specific ID for each trader and ym refer to the year-month variable. For each personid, I want to estimate the number of years over the 10 years period prior to month t. For example, a person trade in 200001, 200301, 200501, 200801, and 201501. Then, for 200301, the number of year is 1. For 200801, the number of year is 3. For 201501, the number of year is 2. 

If the trading year is continuous, I assume I could use by function to calculate the difference between the first trading year and the current trading year and the difference will be the number of year. However, the trading year for each trader is not always continuous. In this case, I do not know how to count the number of observation. Please give me some suggestions. Thanks.

[ballardw]

First thing I would suggest is to use an actual date value instead of number that just looks something like a date.

Then you can easily compare a current records date to the previous using the functions like Intck to get the intervals.

Your problem is missing some details. Such as what your output should look like.

What about when a personid only has ONE record?

Your first personid first two "dates" are May 1999 and March 2010. So, what value goes there?

I would start with something like:

data need;
   set have;
   date = input(put(ym,6.),yymmn6.);
   format date date9.;
run;

data want;
  set need;
  by personid;
  retain firstdate;
  if first.personid then firstdate=date;
  t= intck('year',firstdate, date);
  drop firstdate;
run;

The INTCK function returns the number of intervals between values. You may wan to consider the method optional argument of 'C' for the intck function depending on exactly how you want to treat "year". The default method of D or discrete will return a value of 1 between Dec2000 and Jan2001 to Nov2001 where the C would require the second date to be Dec2001 to get a 1.