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 wich sum 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
Advertisement
Answer
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;