topic Re: Summing values within a column in SAS Programming

Summing values within a column

Lupacante — Mon, 25 Nov 2013 16:16:03 GMT

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

Re: Summing values within a column

LinusH — Mon, 25 Nov 2013 16:35:35 GMT

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.

Re: Summing values within a column

Lupacante — Mon, 25 Nov 2013 16:39:37 GMT

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

Re: Summing values within a column

Reeza — Mon, 25 Nov 2013 17:00:39 GMT

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.

Re: Summing values within a column

Lupacante — Mon, 25 Nov 2013 17:02:54 GMT

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

Re: Summing values within a column

Reeza — Mon, 25 Nov 2013 17:29:05 GMT

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;