topic Re: Sum Only Certain Values in a SAS-EG Column? in SAS Programming

Sum Only Certain Values in a SAS-EG Column?

mdesessa — Fri, 01 Mar 2013 01:40:52 GMT

When making a new computed column in SAS enterprise guide, is there a way to make the value of each cell in the column equal the sum of all the previous values in another column (basically a totalizer)?

Re: Sum Only Certain Values in a SAS-EG Column?

esjackso — Fri, 01 Mar 2013 02:26:36 GMT

Are you creating a dataset or report?

Re: Sum Only Certain Values in a SAS-EG Column?

Haikuo — Fri, 01 Mar 2013 02:38:40 GMT

Not sure how you would do it in EG, but the does the following Base code meet your need?

data test;

do k=1 to 100;

i=100*round(ranuni(0),0.01);

total_i+i;

output;

end;

run;

Haikuo

Re: Sum Only Certain Values in a SAS-EG Column?

mdesessa — Fri, 01 Mar 2013 14:40:01 GMT

I'm creating a dataset.

Re: Sum Only Certain Values in a SAS-EG Column?

esjackso — Fri, 01 Mar 2013 15:20:55 GMT

I dont remember a way to do it using the built in capabilities. I would convert the EG task into programming task (I believe it is a right click option for this - add as a code template - which will probably give you SQL code instead of data step coding if the case you might have to write the datastep yourself) . Then add the retained variable and sum to the code like Hai.Kuo mentioned. Here is another example:

data createddata;

set inputdata;

/*set the initial value*/

totalizer = 0;

/*add each rows total to the running total*/

totalizer = totalizer + total;

/*tell sas to retain the old value to use on the next observation*/

retain totalizer;

run;

Re: Sum Only Certain Values in a SAS-EG Column?

esjackso — Fri, 01 Mar 2013 15:21:53 GMT

If you are creating other things is EG that you dont know how to covert to a datastep - let the group know and we can help with that as well.

Re: Sum Only Certain Values in a SAS-EG Column?

Haikuo — Fri, 01 Mar 2013 16:01:19 GMT

At the risk of being obnoxious, I have to say the following 3 statements:

/*set the initial value*/

totalizer = 0;

/*add each rows total to the running total*/

totalizer = totalizer + total;

/*tell sas to retain the old value to use on the next observation*/

retain totalizer;

can be replaced by just one:

totalizer + total;

I supposed you won't get much in term of performance, it is just lesser typing.

Haikuo

Re: Sum Only Certain Values in a SAS-EG Column?

esjackso — Fri, 01 Mar 2013 16:10:12 GMT

Meant to mention they were the same but replied to fast. Thanks for pointing that out Hai.kuo.

I wasnt sure how much data step understanding there was so I broke down what the automatic sum statement does.