DATA Step, Macro, Functions and more

Finding max across columns summation

Accepted Solution Solved
Reply
Super Contributor
Posts: 401
Accepted Solution

Finding max across columns summation

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.           


Accepted Solutions
Solution
‎08-28-2017 09:23 AM
Super User
Posts: 19,878

Re: Finding max across columns summation

Posted in reply to Astounding

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Finding max across columns summation

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.

 

Super User
Posts: 5,518

Re: Finding max across columns summation

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;

Solution
‎08-28-2017 09:23 AM
Super User
Posts: 19,878

Re: Finding max across columns summation

Posted in reply to Astounding

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 153 views
  • 4 likes
  • 4 in conversation