CODEWITHSUNDEEP
sqlsas
anatolep
anatolep

Reputation: 23

How to add a sum column with respect to a group by

I have a table1:

ZP      age      Sexe    Count
A       40       0       5
A       40       1       3
C       55       1       2

And I want to add a column which sums the count column by grouping the first two variables:

ZP      age      Sexe    Count   Sum 
A       40       0       5       8
A       40       1       3       8
C       55       1       2       2

this is what I do:

CREATE TABLE table2 AS SELECT zp, age, SUM(count) FROM table1 GROUP BY zp, age

then:

CREATE TABLE table3 AS SELECT * FROM table1 NATURAL JOIN table2

But I have a feeling this is a sloppy way to do it. Do you know any better ways? For example with no intermediates tables.

edit: I am using SQL through a proc sql in SAS

Upvotes: 2

Views: 138

Answers (4)

Tom
Tom

Reputation: 51566

PROC SQL does not support enhanced SQL features like PARTITION.

But it looks like you want to include summarized data and detail rows at the same time? If that is the question then PROC SQL will do that for you automatically. If you include in your list of variables to select variables that are neither group by variables or summary statistics then SAS will automatically add in the needed re-joining of the summary statistics to the detail rows to produce the table you want.

proc sql;
  SELECT zp, age, sexe, count, SUM(count)
    FROM table1
    group by zp, age
  ;
quit;

Upvotes: 1

Sean
Sean

Reputation: 1120

data have;
    input ZP $ age Sexe Count;
    datalines;
    A 40 0 5
    A 40 1 3
    C 55 1 2
    ;
run;

proc sql;
    create table want as select 
        *, sum(count) as sum
        from have
        group by zp, age;
quit;

Upvotes: 0

kstats9pt3
kstats9pt3

Reputation: 873

I'm not quite sure if there is a method for a single select statement but below will work without multiple create table statements:

data have;
    length ZP $3 age 3 Sexe $3 Count 3;
    input ZP $ age Sexe $ Count;
    datalines;
    A       40       0       5
    A       40       1       3
    C       55       1       2
    ;
run;

proc sql noprint;
    create table WANT as
    select a.*, b.SUM 
    from
        (select * from HAVE) a,
        (select ZP,sum(COUNT) as SUM from HAVE group by ZP) b
    where a.ZP = b.ZP;
quit;

Upvotes: 2

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

You can use SUM as follows with standard SQL:2003 syntax (I don't know if SAS accepts it):

SELECT zp, age, sexe, count, SUM(count) OVER (PARTITION BY zp, age)
FROM table1;

Upvotes: 0

Related Questions