Help using Base SAS procedures

How can I sum up the rows?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How can I sum up the rows?

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!!


Accepted Solutions
Solution
‎11-06-2012 01:18 PM
Occasional Contributor
Posts: 17

Re: How can I sum up the rows?

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;

or

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How can I sum up the rows?

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;

Frequent Contributor
Posts: 75

Re: How can I sum up the rows?

I mean to get the sum all the values

Super User
Posts: 11,343

Re: How can I sum up the rows?

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.

Solution
‎11-06-2012 01:18 PM
Occasional Contributor
Posts: 17

Re: How can I sum up the rows?

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;

or

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

Frequent Contributor
Posts: 75

Re: How can I sum up the rows?

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

PROC Star
Posts: 7,492

Re: How can I sum up the rows?

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 */

Frequent Contributor
Posts: 75

Re: How can I sum up the rows?

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 7621 views
  • 7 likes
  • 4 in conversation