SQL: Union all expands the result with every repetition

[Konkordanz]

Hi,

with sql I want to create a new dataset that creates also a summarized row. The following code shows what I mean and where my problem is: When I execute this code once, it works. But if I execute it twice or more, the summarized row expands with every repetition. Do you have a clue why this is happen and which way could be the better one?

proc sql;
create table Kategorietest as select
 	sex, 
  	sum(case when age>=13 then weight end) as Teen,
   	sum(case when age<13 then weight end) as Kid,
	count(case when age <13 then age end) as Anzahl
from sashelp.class
group by sex
union all
select "Total",sum(Teen),sum(kid),Sum(anzahl) from Kategorietest;
quit;

[Kurt_Bremser · Accepted solution]

It is always a bad idea to use the same dataset in CREATE TABLE and FROM, PROC SQL should write a message to the log.

Create both parts from the source dataset:

proc sql;
create table Kategorietest as select
  sex, 
  sum(case when age>=13 then weight end) as Teen,
  sum(case when age<13 then weight end) as Kid,
  count(case when age <13 then age end) as Anzahl
from sashelp.class
group by sex
union all
select
  "Total",
  sum(case when age>=13 then weight end) as Teen,
  sum(case when age<13 then weight end) as Kid,
  count(case when age <13 then age end) as Anzahl
from sashelp.class;
quit;

[Konkordanz]

Thank you!