topic Re: Multi-Dimensional Array- Checking for match and take the minimum in SAS Programming

Multi-Dimensional Array- Checking for match and take the minimum

kk13 — Mon, 21 Sep 2020 09:57:15 GMT

I would like to check where a number is located in a multi-dimensional array. Then, I would like to take the minimum of the entire column for that particular row.

For example, I have the following data sets. This is a 3-dminesional with 3 rows and 5 columns.

array temp{3,5} a1_1-a1_5 a2_1-a2_5 a3_1-a3_5;

In the first observation, Check1= 111 is located at temp{2,3}. For non-missing values, I would like to take the minimum. So, min(a2_1-a2_5)=1.

In the second observation, Check1=222 is located at temp{2,4} and min(a2_1-a2_5)=1. Check2=223 is located at temp{3,2} and min(a3_1-a3_5)=3.

data k;

input check1 check2 a1_1 -a1_5 a2_1-a2_5 a3_1-a3_5;

datalines;

111 . 12 122 133 . . 1 11 111 13 . 777 2 . . .

222 223 5 555 232 . . 1 111 123 222 224 11 223 3 . .

;

run;

Thank you

Re: Multi-Dimensional Array- Checking for match and take the minimum

s_lassen — Mon, 21 Sep 2020 10:49:21 GMT

Here is a solution without arrays:

data want;
  set k;
  if not missing(check1) then do;
    pos=whichn(check1,of a1_1--a3_5);
    if pos then select(int(pos/5));
      when(0) min1=min(of a1_:);
      when(1) min1=min(of a2_:);
      when(2) min1=min(of a3_:);
      otherwise;
      end;
    end;
  if not missing(check2) then do;
    pos=whichn(check2,of a1_1--a3_5);
    if pos then select(int(pos/5));
      when(0) min2=min(of a1_:);
      when(1) min2=min(of a2_:);
      when(2) min2=min(of a3_:);
      otherwise;
      end;
    end;  
  drop pos;
run;

If you have more columns, and/or your variables are not ordered on the dataset so that you can use "of a1_1--a3_5", you can do it with a macro:

%macro getmin(check_no,rows=3,columns=5);
%local rowvars i;
%do i=1 %to &columns;
  %let rowvars=&rowvars a&i._:;
  %end;
  if not missing(check&check_no) then do;
    pos=whichn(check&check_no,of &rowvars);
    if pos then select(int(pos/&columns));
      %do i=1 %to &rows;
        when(%eval(&i-1)) min&check_no=min(of a&i._:);
        %end;
      otherwise;
      end;
    end;
%mend;

options mprint;   
data want;
  set k;
  %getmin(1);
  %getmin(2);
  drop pos;
run;

Re: Multi-Dimensional Array- Checking for match and take the minimum

Astounding — Mon, 21 Sep 2020 10:54:34 GMT

Presumably, since you are defining a three-dimensional array, you would have at least a small amount of familiarity with them. Are you able to find which element matches the contents of CHECK1?

The rest of the problem becomes easier if you construct a second array holding the array minimums. For example:

array mins {3} min_row1-min_row3;
if _n_=1 then do _n_=1 to 3;
   min_row1 = min(of a1_1-a1_5);
   min_row2 = min(of a2_1-a2_5);
   min_row3 = min(of a3_1=a3_5);
end;

Then find the element that matches CHECK1, find its row number within the three-dimensional array, and extract the proper element from the MINS array.