BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thdang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AkilanR
Fluorite | Level 6

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

7 REPLIES 7
ballardw
Super User

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;

thdang
Calcite | Level 5

I mean to get the sum all the values

ballardw
Super User

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.

AkilanR
Fluorite | Level 6

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

thdang
Calcite | Level 5

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

art297
Opal | Level 21

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

thdang
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 30095 views
  • 8 likes
  • 4 in conversation