Desktop productivity for business analysts and programmers

Sum some of the specific rows

Reply
Contributor
Posts: 47

Sum some of the specific rows

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

Super User
Posts: 9,874

Re: Sum some of the specific rows

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;  

Contributor
Posts: 47

Re: Sum some of the specific rows

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?

Super User
Posts: 9,874

Re: Sum some of the specific rows

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;  
Super User
Posts: 11,134

Re: Sum some of the specific rows

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.

Ask a Question
Discussion stats
  • 4 replies
  • 416 views
  • 0 likes
  • 3 in conversation