CODEWITHSUNDEEP
sqlsassumproc-sqlenterprise-guide
dingaro
dingaro

Reputation: 2342

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

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 ?

Upvotes: 1

Views: 1815

Answers (2)

Negdo
Negdo

Reputation: 532

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.

Upvotes: 0

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

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

Upvotes: 0

Related Questions