Hi, I have a question regarding how to calculate three year moving or rolling sum for each ID in a panel dataset. The variables of interest are as follows: Year ID NUniquePatents Sum of N unique Patents by ID and for a three year moving window 1901 A 1 2 1901 A 1 2 1901 A 0 2 1915 B 1 1 1915 A 0 2 1920 B 1 2 1920 A 0 2 1924 A 1 2 1924 A 1 2 1924 B 0 2 1924 A 0 2 1936 A 0 2 1936 B 1 2 I am looking for a code to calculate the last column highlighted in red. In column three-1 represents unique value and 0 represents non-unique value. I want to sum up these 0s and 1s for each ID during a three year moving window. Example of values in the last column: first time period: 1901, second time period: 1901+1915, third time period: 1901+1915+1920, fourth time period: 1915+1920+1924, fifth time period: 1920+1924+1936. I have already calculated the third column-i.e. number of unique patents. Now, I want to sum the number of unique values by ID and by three year moving window. So for example, from the above table, assume that 1901 is the first year in the dataset , then the sum of unique value should be 2 for A. In 1915, the unique value should once again be equal to 2 as there are no new unique values for A in this time period (1901 and 1915). In 1920, A has no unique values , so for this time period (1901, 1915 and 1920) the sum should still be 2. Now for 1924, A has 2 unique values, but the sum should be calculated for the previous two years window and the current year (i.e. sum of A for years 1924, 1920 and 1915). In 1924, A has 2, 1920 A has 0 and in 1915 A has 0. Hence the sum of A for 1924 will be 2. To calculate value of A for 1936, sum of 1936, 1924 and 1920 should be considered, hence A has a value of 0 in 1936, in 1924 it has a unique value of 2 and it has a value of 0 in 1920. Hence for 1936 A is 0+2+0 which is 2. I am looking at the third column when I count the number of unique values. The same is to be done for B and other IDs. I did try using proc SQL in SAS with a group by statement but it didn't produce the desired nuanced result. Please let me know if any of you can think of a solution. Thank you for your help.
... View more