Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Sum by IDs and variables

how can I sum over a set of variables by ID?

Suppose to have the following:

data DB;
  input ID :$20. Value1 Value2 Value3; 
cards;
0001 33    .     .
0001  .   22     .
0001  .    .     .
0001  .    .     6
0002  .    32    .
0002  .    .     4
0003  .    2     90
0003  3    .     .
;

Is there a way to get the following?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

data DB1;
  input ID :$20. Value1 Value2 Value3  Total; 
cards;
0001 33    .     .    61
0001  .   22     .    .
0001  .    .     .    .
0001  .    .     6    .
0002  .    32    .   36
0002  .    .     4    .
0003  .    2     90  95
0003  3    .     .    .
;

The sum should be placed at the first occurrence/position of each ID.

Thank you in advance

>Solution :

Try this

data db1;
   do until(last.ID);
      set DB;
      by ID;
      if first.ID then Total = 0;
      Total + sum(of Value:);
   end;

   do until(last.ID);
      set DB;
      by ID;
      if not first.ID then Total = .;
      output;
   end;
run;

Result:

ID    Value1  Value2  Value3  Total
0001  33      .       .       61
0001  .       22      .       .
0001  .       .       .       .
0001  .       .       6       .
0002  .       32      .       36
0002  .       .       4       .
0003  .       2       90       95
0003  3       .       .       .
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading