## Conditional sum across columns

Solved
Frequent Contributor
Posts: 138

# Conditional sum across columns

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.

Accepted Solutions
Solution
‎07-02-2014 02:56 PM
Super User
Posts: 8,114

## Re: Conditional sum across columns

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;

All Replies
Solution
‎07-02-2014 02:56 PM
Super User
Posts: 8,114

## Re: Conditional sum across columns

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;

🔒 This topic is solved and locked.