Summing values within a column

[Lupacante]

Hi all,

I have the following dataset:

_name_     col1

val1           14

val2           1

val3           5

val4           0

val5           2

val6           1

val7           6

val8           0

val9           0

val10         0

val11         0

val12         0

val13         0

val14        40

val15        7

val16        440

val17        0

I would like to make val13 the sum of val1-val12 and val17 the sum of val14-val17. So my hope is that my dataset will look like this:

_name_     col1

val1           14

val2           1

val3           5

val4           0

val5           2

val6           1

val7           6

val8           0

val9           0

val10         0

val11         0

val12         0

val13         29

val14        40

val15        7

val16        440

val17        487

Any ideas?

Thanks in advance,

Marco

[LinusH]

Your requirement seems a bit...odd. Could you elaborate about the reasons for this calculation?

Your input data seems to be the result from a PROC TRANSPOSE or similar, it would help to know to structure of the original data, and the business requirement.

Form what I see now, you could accomplish this by using a data step with retain, but not really a best practice, since it would require some sort of hard coded values of some sort.

[Lupacante]

Hi LinusH,

thanks for your reply.

The reasons for this requirement is because I would like to directly paste output into a Powerpoint presentation. At the moment I paste it into Excel, use the built-in sum function, then paste into Powerpoint.

As it's a regular process, it would save a lot of time to be able to skip the Powerpoint part.

Thank you,
Marco

[Reeza]

Is that actually how your original data looks like? IE is 13/17 always in the data with a 0 already?

Will it always be 13 and 17, or are you summarizing different categories?

You can easily do totals/subtotals using a variety of procedures (report/tabulate) that could then be exported to PowerPoint via Excel (minus the sum part) or directly to PPT if you have 9.4.

[Lupacante]

HI Reeza,

yes, that is actually how it looks. 13/17 are always 0 already.

Yes, it will always be 13 and 17.

That's great - could you please post an example of how to do a subtotal with report/tabulate?

Thank you very much,
Marco

[Reeza]

For report or tabulate I'd back up step. Looking at your column names I'm assuming they're coming out of a different proc.

If you just want do always add up 13/17 the following code will work. It isn't recommended, I'd rather build the summation logic using the raw data.

data have;

input _name_  $   col1 ;

cards;

val1     14

val2     1

val3     5

val4     0

val5     2

val6     1

val7     6

val8     0

val9     0

val10    0

val11    0

val12    0

val13    0

val14    40

val15    7

val16    440

val17    0

;

run;

data want;

set have;

retain running_total;

if _name_ not in ('val13', 'val17') then running_total+col1;

else do;

    col1=running_total;

    running_total=0;

end;

run;