Sum Previous Years rows in SAS, 3 groups

Question

I would like to Sum, for each Codinv and Class, values of previous years listed in column D.

Thank you Rigerta. Here is my New Request. Now that I think about that, when there is just one row per CodInv per Class, it should show the same value as D. Hence, I would like a new column to be calculated as follows

 Codinv    Class      year        D       NewColumn   
----------------------------------------------------------
  13       C08F       1977        5         5
  76       C01B       1999        1         1
  76       C21D       2005        2         2
  76       C23C       1998        2         2
  76       C23C       1999        2         4

I would change the code as follows, but it still does not work

As I read online, I tried with

data Want;                                                           
set Have;                                                           
by Codinv Class year;                                                             
retain NewColumn;                                              
if first.Class then NewColumn=D; output; 
if last.year NewColumn=NewColumn+D;                             
run;

It worked well with another analysis I had to do where I sorted by Codinv and Year only, now that I am doing it with three I tried different variations, but it is showing missing data for all rows or 0... Can you help me out? Forever Grateful

Answer 1

You're close with your attempt, I've modified it to produce the desired output. A summary of the changes I've made are :

• Removed the retain statement. The method I've used adopts an automatic retain, so isn't necessary.
• Initialise NewColumn to 0 each time the class changes
• Add D to NewColumn for each row. (x+y, as used here, creates an implied retain)
• Removed the output statement. This is implied at the end of the data step, so isn't needed.
• Removed the if last.year... line as it is not necessary

• Strictly speaking, having year in the by statement isn't necessary, but it is useful to keep in to ensure the data is sorted properly.

  data have;
  input Codinv Class $ year D;
  datalines;
  13       C08F       1977        5
  76       C01B       1999        1
  76       C21D       2005        2
  76       C23C       1998        2
  76       C23C       1999        2
  ;
  run;
  
  data Want;                                                           
  set Have;                                                           
  by Codinv Class year;                                                             
  if first.Class then NewColumn=0;
  newcolumn+D;
  run;