topic Re: Sum function understanding in SAS Programming

Sum function understanding

Babloo — Tue, 06 Mar 2018 08:10:24 GMT

Appreciate if someone of you help me understand the difference between the following two sum functions. I just wanted to know the difference between the two.

sum ('7101100000'n ,
         '7101100010'n ,
         '7101101000'n 
         ) as PREM_AMT

SUM('6131112300'n) AS SUM_of_6131112300

Re: Sum function understanding

Kurt_Bremser — Tue, 06 Mar 2018 08:15:59 GMT

The first sums several variables within a single row (observation), the second sums one variable across observations.

Re: Sum function understanding

Babloo — Tue, 06 Mar 2018 08:54:44 GMT

Could you please clarify the meaning of "sums one variable across observations"?

Re: Sum function understanding

RW9 — Tue, 06 Mar 2018 09:12:01 GMT

VAR1 VAR2 VAR3

abc def ghi

Sum across observations means the value from one row of abc + def + ghi

Sum column means all from one column abc + abc

Re: Sum function understanding

Kurt_Bremser — Tue, 06 Mar 2018 09:20:29 GMT

@Babloo wrote:

Could you please clarify the meaning of "sums one variable across observations"?

Should have said "over several observations", as across might be misleading.

Also see this result of a google search for "sas sql summary functions".

Re: Sum function understanding

Babloo — Tue, 06 Mar 2018 10:45:23 GMT

When you say "Sum column means all from one column abc + abc", I will be getting the same value for all the observations if new variable VAR4 is being created?

Re: Sum function understanding

Babloo — Tue, 06 Mar 2018 10:55:43 GMT

When you say "over several observations" whether I will be getting the same value for all the observations if new variable VAR4 is being created?

Re: Sum function understanding

Kurt_Bremser — Tue, 06 Mar 2018 11:06:27 GMT

Just try it.

Run this code:

data have;
input x y;
cards;
1 2
3 4
5 6
;
run;

proc sql;
create table want1 as
select sum(x,y) as z
from have;
create table want2 as
select sum(x) as x, sum(y) as y
from have;
quit;

proc print data=want1 noobs;
run;
proc print data=want2 noobs;
run;

and look at the results.

Re: Sum function understanding

ballardw — Tue, 06 Mar 2018 18:23:35 GMT

History aside:

SAS started in 1966 or thereabouts and started creating data step programming.

SQL started a few years later.

Each had their own idea of "sum" because of how the two packages were used.

Years later SAS incorporated the Proc SQL to have many of the features of SQL and hence used the SQL core features like SUM as an aggregate function across rows so existing SQL programs were easier to convert (and to not confuse SQL programmers).

Note that SUM in proc means, summary, tabulate, report and print are much like the aggregate Sum in proc sql if that helps any.

So this issue of which is which comes up from occasional users of both paths.

The computer world if rife with such "similarities". I learned one OS that used CAT for catalog (listing of files), UNIX didn't like that at all when I tried that when I was learning the little I know of Unix. {For non-Unix programmers CAT concatenates files, very different behavior}.

Attention to detail and syntax guides are your friends.