How to create new column in PROC SQL with sum of values per month in SAS enterprise Guide?

Question

I have query in proc sql in SAS Enterprise Guide like below:

proc sql;
select 
event
, mnth
, value
from table1
order by mnth, name
;quit;

Above proc sql query gives result as below:

event| mnth  | value
-----|-------|-------
ABC  | APRIL | 3E-6
ABC  | APRIL | 0,27950
ABC  | APRIL | 0,556
ABC  | MAY   | 0,228
...  | ...   | ...

And I need to create new column where will be showed sum of values per mnth, so as a result I need something like below (Of course we should take into account also values like '3E-6' if it is possible):

event| mnth  | value   | sum
-----|-------|---------|-----
ABC  | APRIL | 3E-6    |
ABC  | APRIL | 0,27950 |
ABC  | APRIL | 0,556   | 0,8355
ABC  | MAY   | 0,228   | 0,228
...  | ...   | ...     | ...

How can I modify my code in proc sql so as to achieve result like above ?

What do you mean by 3E-6? Is your VALUE variable character? To sum it up it needs to be numeric, so you would need to include a step to convert it to a number first. If it is already a number then probable you are seeing3E-6 in the printout because the width on the format used to display it is too small to show 3 preceded by the decimal place and 5 zeros. That is what the BEST format does, it finds the "best" way to display the number in a limited number of characters. — Tom, Aug 29 '22 at 20:15
Is MNTH numeric or character? Character months will NOT sort in chronological order. — Tom, Aug 29 '22 at 20:17
Why are you only showing the monthly sum on one of the observations for the month? What does that mean? — Tom, Aug 29 '22 at 20:20

score 0 · Answer 1 · answered Aug 29 '22 at 19:41

0

A data step would be easier. The code below cumulatively sums the value of each month within each group, then outputs the result only at the last month.

data want;
    set have;
    by event mnth;

    if(first.mnth) then s = 0;

    s+value;
    
    if(last.mnth) then sum = s;

    drop s;
run;

event	mnth	value	sum
ABC	APRIL	3E-6	.
ABC	APRIL	0.2795	.
ABC	APRIL	0.556	0.835503
ABC	MAY	0.228	0.228

answered Aug 29 '22 at 19:41

Stu Sztukowski

10,597
1
12
21

hmmm but I do not know how to implement your solution to my code, could you add your lines to my code so as to see sull solution please ? – dingaro Aug 29 '22 at 19:47
Those are the lines to add to your code. – Stu Sztukowski Aug 29 '22 at 19:49
I have error: "File: WORK.HAVE.DATA does not exists" – dingaro Aug 29 '22 at 19:53
Change `have` to your input dataset name. If you receive an error that says it is not sorted in the right order, sort your dataset by `event` and `mnth` in that order. – Stu Sztukowski Aug 29 '22 at 20:01

score 0 · Answer 2 · answered Aug 30 '22 at 07:05

If you really want it done in proc SQL you can do it like this:

proc SQL;
    select *, sum(value) as sum
        from table1
            group by mnth
            order by mnth
    ;
run;

You get your sum in every row thou (and having sum only in last row is far easier to do in data step). But you should care that your value column is numeric. If not, you should change it to one: either at table generation, or by using INPUT function.

How to create new column in PROC SQL with sum of values per month in SAS enterprise Guide?

2 Answers2