Hi Everyone,  I have the following data structure : Inventor 1stfirm 2ndfirm movedate patent yearPatent A 390 909 1973 37237239 1969 A 390 909 1973 78877878 1969 A 390 909   87090990 1970 A 390 909   98908087 1970 A 390 909   44347799 1970 A 390 909   77879808 1971 A 390 909   78787099 1972 A 390 909   87900800 1972 A 390 909   34778790 1973 A 390 909   87989898 1974 A 390 909   23477879 1975 A 390 909   87990008 1975 A 390 909   34778879 1976 A 390 909 1973 79887877 1977   I am trying to count the number of patents an inventor has produced 3 years right before leaving the 1stfirm and 3 years right after moving to the 2nd firm. So in the example above as the movedate is 1973, I want to count the patents produced by A in 1970,1971,1972 only and name it as premoving and then count patents produced by A in 1974,1975,1976 only and name it as postmoving. Using this logic, I want to first count number of patents per year and then number of patents for 3 years per inventor.   While I know the basic proc sql command and group by to do this, I wanted to ask the experts here as this is a large dataset and I did not want to make mistakes. Please let me know if there is a simple robust code to do this. Thank you once again for the amazing help you all provide    ... View more

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