topic Re: Finding max across columns summation in SAS Programming

Finding max across columns summation

podarum — Tue, 22 Aug 2017 18:46:12 GMT

Hi, I'm trying to figure out how I can sum across columns and check to see which is highest (or sort them).

Here's what I mean.

I have the data

Acct Prov Field_1 Field_2 Msg1 Msg2 Msg3 ..... Msg999

123 ON A 123 1 1 0 0

234 ON B 124 0 1 1 1

235 BC A 154 1 1 1 0

256 YK D 146 0 0 1 1

938 MB F 156 0 1 1 1

Pretend I have 100,000 records, I want to see by addition which message pops up to most from Msg1 to Msg999 (may be some missing like Msg 903, etc. is not a columns.

for Example Msg1 : 234 times

Msg2 : 3484 times,

Msg3 : 23847 times,

Msg4 : 1 time

and so on...

Thanks.

Re: Finding max across columns summation

ballardw — Tue, 22 Aug 2017 19:05:12 GMT

proc means data=yourdataset sum;

var msg: ;

run;

the msg: the : denotes a variable list of all variables whose names start with msg.

One of the very nice things of 0/1 coded variables is that the sum = count of values = 1. The mean gives you a percentage if values=1.

Re: Finding max across columns summation

Astounding — Tue, 22 Aug 2017 19:18:55 GMT

With so many variables, you probably want the machine to find the largest values. For example:

proc summary data=have;

var msg: ;

output out=sums (keep=msg: ) sum=;

run;

data want;

set sums;

array counts {*} msg: ;

do i=1 to dim(counts);

variable = vname(counts{i});

count = counts{i};

output;

end;

keep variable count;

run;

proc sort data=want;

by descending count;

run;

proc print data=want;

var variable count;

run;

Re: Finding max across columns summation

Reeza — Tue, 22 Aug 2017 21:33:15 GMT

Use STACKODS in combination with PROC MEANS:

proc means data=fake stackods sum mean;
var msg1-msg100;
ods output summary=want;
run;

proc sort data=want; 
by descending sum;
run;