topic Re: Conditional sum across columns in SAS Programming

Conditional sum across columns

Walternate — Wed, 02 Jul 2014 18:13:49 GMT

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.

Re: Conditional sum across columns

Tom — Wed, 02 Jul 2014 18:56:20 GMT

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;