SAS duplicate maximum values across rows

3
votes

I have a data set that looks like this:

company Assets Liabilities strategy1 strategy2 strategy3.....strategy22 
   1       500      500        0          50        50            50            
   2       200      300        33         30        33             0

My goal is to find the maximum value across the row for all strategies (strategy1 - strategy22), and basically bucket the company by the strategy they use. The problem comes when some companies have the same maximum value under multiple strategies. In this case I would want to place the firm into multiple buckets. The final dataset would be something like this:

company Assets Liab. strategy1 strategy2 strategy3.....strategy22 Strategy
   1       500   500     0          50        50            50     Strategy2    
   1       500   500     0          50        50            50     Strategy3
   1       500   500     0          50        50            50     Strategy22

Etc. The end goal is to be able to run a proc means on the company's assets, liabilities, etc. by strategy. So far I have been able to achieve a dataset close to what I would like, but in the "Strategy" column I can't get it so SAS doesn't always output the first strategy with the maximum value.

Data want;
  set have;
   MAX = max(of strategy1-strategy22);
   array nums {22} strategy1-strategy22;
     do _n_=1 to 21;
       count=1;
     do _i_ = _n_+1 to 22;
        if nums{_n_} = nums{_i_} AND nums{_i_} ne 0 then count + 1;
     end;
     if count > maxcount then do;
        mode = nums{_n_};
        maxcount = count;
     end;
  end;
 Run;
 Data want2;
  set want (where=( maxcount > 1 AND Mode = Max));
  by company;
   strat=1;
    do until (strat gt maxcount);
      output;
      strat = strat +1;
    end;
 Run;

Basically, I computed the mode and the count of identical maximum values and if maxcount > 1 and mode = max then I output identical observations. However, I am stuck regarding how to get SAS to output different strategies if there are multiple maximum values that are the same.

2
sas
I'm not familiar with SAS, but what I would do is loop through each strategy for the company to find what the highest score is (for example, 50), then loop through each strategy and add it to a bucket for a strategy if it's score for that strategy is equal to the max score that was calculated earlier. As I said, I don't really understand your code, but it looks like you have already implemented the first half. - Nick Mertin

2 Answers

3
votes

That seems more complicated than you need.

data want;
  set have;
  array strategies[22] strategy1-strategy22;
  do strategy = 1 to dim(strategies);
    if strategies[strategy] = max(of strategies[*]) then output;
  end;
run;
0
votes

Why not just output the the row if the strategy column matches the MAX?

My array language is off, but here is some pseudo code to do what I'm thinking...

If the column you're on has the value EQ MAX, then output that row with the strategy column set to the strategy that you're looking at:

Data want;
  set have;
   MAX = max(of strategy1-strategy22);
   array nums {22} strategy1-strategy22;
     do i = _n_+1 to 22;
        if nums{i} eq MAX then do;
            strategy = "strategy" + i
            output;
     end;
 Run;