CODEWITHSUNDEEP
sasrolling-computation
Neal801
Neal801

Reputation: 307

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

I have a sample that include two variables: ID and ym. ID id refer to the specific ID for each trader and ym refer to the year-month variable. And I want to create a variable that show the number of years over the 10 years period prior month t as shown in the following figure.

ID  ym  Want
1   200101  0
1   200301  1
1   200401  2
1   200501  3
1   200601  4
1   200801  5
1   201201  5
1   201501  4
2   200001  0
2   200203  1
2   200401  2
2   200506  3

I attempt to use by function and fisrt.id to count the number.

data want;
set have;
want+1;
by id;
if first.id then want=1;
run;

However, the year in ym is not continuous. When the time gap is higher than 10 years, this method is not working. Although I assume I need to count the number of year in a rolling window (10 years), I am not sure how to achieve it. Please give me some suggestions. Thanks.

Upvotes: 0

Views: 662

Answers (2)

Tom
Tom

Reputation: 51566

Just do a self join in SQL. With your coding of YM it is easy to do interval that is a multiple of a year, but harder to do other intervals.

proc sql;
create table want as 
  select a.id,a.ym,count(b.ym) as want 
  from have a 
   left join have b
   on a.id = b.id
   and (a.ym - 1000) <= b.ym < a.ym
  group by a.id,a.ym
  order by a.id,a.ym
;
quit;

Upvotes: 5

Jeremy Miller
Jeremy Miller

Reputation: 146

This method retains the previous values for each ID and directly checks to see how many are within 120 months of the current value. It is not optimized but it works. You can set the array m() to the maximum number of values you have per ID if you care about efficiency.

The variable d is a quick shorthand I often use which converts years/months into an integer value - so

200012 -> (2000*12) + 12 = 24012
200101 -> (2001*12) + 1 = 24013
time from 200012 to 200101 = 24013 - 24012 = 1 month

data have;
   input id ym;
datalines;
1   200101  
1   200301  
1   200401  
1   200501  
1   200601  
1   200801  
1   201201  
1   201501  
2   200001  
2   200203  
2   200401  
2   200506  
;

proc sort data=have;
   by id ym;

data want (keep=id ym want);
   set have;
   by id;
   
   retain seq m1-m100;
   
   array m(100) m1-m100;
   
   ** Convert date to comparable value **;
   d = 12 * floor(ym/100) + mod(ym,10);
   
   ** Initialize number of previous records **;
   want = 0;
   
   ** If first record, set retained values to missing and leave want=0 **;
   if first.id then call missing(seq,of m1-m100);
   ** Otherwise loop through previous months and count how many were within 120 months **;
   else do;
      do i = 1 to seq;
         if d <= (m(i) + 120) then want = want + 1;
      end;
   end;
   
   ** Increment variables for next iteration **;
   seq + 1;
   m(seq) = d;
 
 run;
 
 proc print data=want noobs;

Upvotes: 1

Related Questions