topic Re: How can I sum up the rows? in SAS Procedures

How can I sum up the rows?

thdang — Tue, 06 Nov 2012 15:57:34 GMT

Hi, can someone tell me how can i calculate the sum of the value for each month of this table? I have this table:

ID Date code value

so all those IDs have the same code I want as the results:

code Date value

101048 01/31/2006 7573.3 (the sum of all the value of 01/31/2006)

101048 02/28/2006 (the sum of all the value of 02/28/2006)

...

Thank you!!

Re: How can I sum up the rows?

ballardw — Tue, 06 Nov 2012 16:08:49 GMT

Do you want the sum by Month, as in the top of your question, or by Date as implied by your parenthetical remarks at the end?

Both can be accomplished by use of PROC MEANS or SUMMARY and a format if the date you are showing is a SAS date and not a text string. If you only have one date per month as appears in your example data then no other format would be needed

proc means data=<your data set name >;

class code date;

var value;

/* use the following line if there are more than one date per month in your data*/

format date MonYYy.; /* this will result in things like May2006 */

run;

Re: How can I sum up the rows?

thdang — Tue, 06 Nov 2012 16:20:16 GMT

I mean to get the sum all the values

Re: How can I sum up the rows?

ballardw — Tue, 06 Nov 2012 18:06:08 GMT

Did you see if the example code provides your desired output?

I should have had

Proc means data=<> sum;

to restrict output to sums only, but the default output should have had sum.

Re: How can I sum up the rows?

AkilanR — Tue, 06 Nov 2012 18:18:09 GMT

Hi,

If you want to take the sum of value for each date in a table then

Proc sql;

Create table Example as

Select code

,date

,SUM(VALUE) AS value

From <dataset-name>

group by

code

,date

;

Quit;

I hope below query will help you .If you want to get the sum of the value month wise then

Proc sql;

Create table Example as

Select code

,SUM(VALUE) AS value

From <dataset-name>

group by

code

,year(date)

,month(date)

;

Quit;

Regards

Re: How can I sum up the rows?

thdang — Tue, 06 Nov 2012 20:07:44 GMT

Thank you for your answer. How can I add more variable to the table which involves more calculation? I've tried this and it works

Proc sql;

Create table sasdata.fund_characteristics as

Select date, code, sum(value) as value

from <dataset-name> group by code, date;

quit;

how can i add other variable like (value1*value2)/sum(value1) as value2 ? so like

Proc sql;

Create table sasdata.fund_characteristics as

Select date, code, sum(value1) as value1,

(value1*value2)/sum(value1) as value2

from <dataset-name> group by code, date;

quit; => this does not work anymore

Re: How can I sum up the rows?

art297 — Tue, 06 Nov 2012 21:00:55 GMT

Assuming you have assigned the library sasdata, and have replaced <dataset-name> with the name of an existing dataset, you only have one omission, namely that when you refer to value1, you have to precede it with the string calculated. And, you can't call for the sum of a calculated variable.

Are you trying to do something like the following:

Proc sql;

Create table want as

Select name, age, sum(height) as value1,

(calculated value1*weight)/sum(height) as weight

from sashelp.class

group by name, age

;

quit; /* this does not work anymore */

Re: How can I sum up the rows?

thdang — Tue, 06 Nov 2012 21:24:51 GMT

yes that is what I'm trying to do, thank you very much. I've got it