CODEWITHSUNDEEP
datasetsas
Sam
Sam

Reputation: 1

Sum of multiple variables by group

I have a dataset with over 900 observations, each observation represents the population of a sub-geographical area for a given year by gender (male, female, all) and 20 different age groups.

I have dropped the variable for the sub-geographical area and I want to collape into the greater geographical area (called Geo).

I am having a difficult time doing a SUM or PROC MEANS because I have so many age groups to sum up and I am trying to avoid writing them all out. I want to collapse across the group year, geo, sex so that I only have 3 observations per Geo (my raw data could have as many as 54 observations).

This is an example of what a tiny section of the raw data looks like:

Year    Geo     Sex     Age0005     Age0610     Age1115     (etc)
2010    1       1       92          73          75
2010    1       2       57          81          69
2010    1       3       159         154         144
2010    1       1       41          38          43
2010    1       2       52          41          39
2010    1       3       93          79          82
2010    2       1       71          66          68
2010    2       2       63          64          70
2010    2       3       134         130         138
2010    2       1       32          35          34
2010    2       2       29          31          36
2010    2       3       61          66          70

This is how I want it to look:

Year    Group   Sex     Age0005     Age0610     Age1115     (etc)
2010    1       1       133         111         118
2010    1       2       109         122         08
2010    1       3       252         233         226
2010    2       1       103         101         102
2010    2       2       92          95          106
2010    2       3       195         196         208

Any ideas? Please help!

Upvotes: 0

Views: 2402

Answers (2)

user667489
user667489

Reputation: 9569

You don't have to write out each variable name individually - there are ways of getting around that. E.g. if all of the age group variables that need to be summed up start with age then you can use a : wildcard to match them:

proc summary nway data = have;
  var age:;
  class year geo sex;
  output out = want sum=;
run;

If your variables don't have a common prefix, but are all next to each other in one big horizontal group in your dataset, you can use a double dash list instead:

proc summary nway data = have;
  var age005--age1115; /*Includes all variables between these two*/
  class year geo sex;
  output out = want sum=;
run;

Note also the use of sum= - this means that each summarised variable is reproduced with its original name in the output dataset.

Upvotes: 3

Heidi
Heidi

Reputation: 96

I personally like to use proc sql for this, since it makes it very clear what you're summing and grouping by. 

    data old ; 
    input Year Geo Sex Age0005 Age0610 Age1115 ; 
    datalines;
    2010 1 1 92 73 75
    2010 1 2 57 81 69
    2010 1 3 159 154 144
    2010 1 1 41 38 43
    2010 1 2 52 41 39
    2010 1 3 93 79 82
    2010 2 1 71 66 68
    2010 2 2 63 64 70
    2010 2 3 134 130 138
    2010 2 1 32 35 34
    2010 2 2 29 31 36
    2010 2 3 61 66 70
    ;
    run;

    proc sql ; 
     create table new as select 
      year
        , geo label = 'Group'
        , sex
        , sum(age0005) as age0005
        , sum(age0610) as age0610
        , sum(age1115) as age1115
        from old 
        group by geo, year, sex ; 
    quit;

Upvotes: 1

Related Questions