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

Question

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

Answer 1

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;