SAS: Counting Unique Values for Previous Years (keeping count)

Question

I was wondering the most efficient way of keeping count of observations in prior years:

I have data that looks like this:

id store  year count 

1  green  2011  3        
2  green  2011  3        
3  green  2011  3        
4  green  2012  1        
5  blue   2012  1        
6  blue   2013  1        
7  red    2011  1        
8  red    2012  1        
9  red    2013  1   

Basically, I counted frequency of ids and grouped them by store and year, to keep track of how many observations shopped at the store for that year.

Now, what I want to do is have a count of observations for previous year(s).

Desired Output:

> id store  year count Count_Previous_Years
> 
> 1  green  2011  3        0
> 
> 2  green  2011  3        0
> 
> 3  green  2011  3        0
> 
> 4  green  2012  1        3
> 
> 5  blue   2012  1        0
> 
> 6  blue   2013  1        1
> 
> 7  red    2011  1        0
> 
> 8  red    2012  1        1
> 
> 9  red    2013  1        2

Is there a way to do this in SQL? i.e. Count (id) with a "where" statement for prior year?

Answer 1

this might be done in a data step as follows:

proc sort data=a;                                                                                                                       
by store year;run;                                                                                                                      

 data b;                                                                                                                            
 set a;                                                                                                                                  
  by store year;                                                                                                                          
  retain hold_year count2 previous; 

  if first.store then do;                                                                                                                 
     hold_year = year;                                                                                                                       
     previous = 0;                                                                                                                           
     count2 = 0;                                                                                                                             
  end;                                                                                                                                    
  if year > hold_year then do;                                                                                                            
     previous = count2; 
     hold_year = year;                                                                                                       
 end;                                                                                                                                    
 count2=count2+1;                                                                                                                        
 run;