Conditional sum across columns

[Walternate]

Hi all,

I have data consisting of member ID and 30 variables. The 30 variables are in 3 sets. Basically, a participant can have up to 10 mountains listed. Each mountain will then have another column indicating the country where that mountain is located. Finally, there will be a third column indicating how many times a participant has climbed that mountain.

Like this:

ID     Mountain1       Mountain1_country     Mountain1_climb      Mountain2            Mountain2_country  Mountain2_climb......Mountain10  etc

1   Mount McKinley      US                                   2                Mont Blanc                 France                         4

2   Mount Pico          Portugal                              3                 Mount McKinley          US                              3

What I want is to create columns that sum the climb variables by country--that is, how many times did each participant climb mountains in the US, France, etc.

Any help is much appreciated.

[Tom · Accepted solution]

Much easier if you convert the data to one row per person-mountain rather on row per person.

I changed your variable names to have the number as the suffix to make it easier to write the code for the example data.

data have;

infile cards dsd dlm='|' truncover ;

length ID 8 Mountain1-Mountain2 $20

Mountain_Country1-Mountain_Country2 $20

Mountain_Climb1-Mountain_Climb2 8

;

input ID Mountain1 Mountain_country1 Mountain_climb1

Mountain2 Mountain_country2 Mountain_climb2

;

cards;

1|Mount McKinley|US|2|Mont Blanc|France|4

2|Mount Pico|Portugal|3|Mount McKinley|US|3

run;

data vertical;

  length id 8 n 8 Mountain $20 Country $20 Climb 8 ;

  set have ;

  array a1 mountain1-mountain2;

  array a2 mountain_country: ;

  array a3 mountain_climb: ;

  do n=1 to dim(a1);

   mountain = a1(n);

    country = a2(n);

    climb = a3(n);

      if not missing(mountain) then output;

  end;

  keep id -- climb ;

run;

proc means nway sum ;

  class country ;

  var climb;

run;