topic Sum some of the specific rows in SAS Enterprise Guide

Sum some of the specific rows

lerdem — Fri, 31 Jul 2015 12:20:10 GMT

The example table i have is:

Var1 Var2 Var3 Var4 Var5

Gender East

Male 25 28 30 East

Female 12 45 14 East

Gender West

Male 10 5 4 West

Female 4 14 3 West

I need to create another values as below

Var1 Var2 Var3 Var4 Var5

Gender East

Male 25 28 30 East

Female 12 45 14 East

Gender West

Male 10 5 4 West

Female 4 14 3 West

Gender East-West

Male 35 33 34 East-West

Female 16 59 17 East-West

Re: Sum some of the specific rows

Ksharp — Fri, 31 Jul 2015 13:10:17 GMT

What is it for ?

Var1 Var2 Var3 Var4 Var5

Gender East

Male 25 28 30 East

Female 12 45 14 East

Gender 14 25 10 West

Male 10 5 4 West

Female 4 14 3 West

You want a table or a report ?

Code: Program

data have;
input Var1 $   Var2   Var3   Var4   Var5 : $20.;
cards;
  Gender . . . East
  Male 25 28 30 East
  Female 12 45 14 East 
  Gender . . . West
  Male 10 5 4 West
  Female 4 14 3 West 
;
run;
proc sql;
create table want(drop=group) as
 select 1 as group, * from have
 union
 select 2 as group,var1,sum(var2) as var2,sum(var3) as var3,sum(var4) as var4,'East-West' as var5 length=20
  from have
   group by var1
 order by group,var5;
quit;

Re: Sum some of the specific rows

lerdem — Fri, 31 Jul 2015 13:37:24 GMT

Thank you, it works for this example. But i have more then that. In my real work i t doesn';t work. I have more groups. So should i have

Var1 Var2 Var3 Var4 Var5

Gender East

Male 25 28 30 East

Female 12 45 14 East

Gender West

Male 10 5 4 West

Female 4 14 3 West

Gender 5 1 2 Osceola

Female 1 74 21 Osceola

Male 41 25 32 Osceola

Is that the same?

Re: Sum some of the specific rows

Ksharp — Fri, 31 Jul 2015 14:41:18 GMT

Code: Program

data have;
input Var1 $   Var2   Var3   Var4   Var5 : $20.;
cards;
  Gender . . . East
  Male 25 28 30 East
  Female 12 45 14 East
  Gender . . . West
  Male 10 5 4 West
  Female 4 14 3 West  
  Gender . . . Osceola
Female 1 74 21 Osceola
Male 41 25 32 Osceola
;
run;
proc sql;
select distinct var5 into : list separated by '-' from have;
create table want as
 select  * from have
 union
 select var1,sum(var2) as var2,sum(var3) as var3,sum(var4) as var4,"&list" as var5 length=20
  from have
   group by var1
 order by var5;
quit;

Re: Sum some of the specific rows

ballardw — Fri, 31 Jul 2015 14:51:04 GMT

This is the point where I ask: What will you do with the resulting data set? You now have records that are not obvious as being composites of others. The apparent task is much more of a REPORT.

And also provide details of "how it doesn't work". We don't know if 1) there was no output, 2) the output was not as needed for the provided data, 3) there was a syntax error and the code didn't run or 4) something related to incomplete description of task.