topic Re: Find maximum value of each array variable and assign it to last row cell in SAS Programming

Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 07:09:21 GMT

Hello, all.

I have a dataset looks like below:

data test;
    input Var1-Var11@;
cards;
0 1 2 3 4 5 6 7 8 9 .
9 0 1 2 3 4 5 6 7 8 .
8 9 0 1 2 3 4 5 6 7 .
. . . . . . . . . . .
;
run;

And my purpose is to find out each row & column maximum values and to assign the maximum value at the corresponding rows and columns. (Place the find out maximum values at the placeholder "." in the "test" dataset.)

I tried it below:

data findmax;
    set test;
    Var11 = max(of Var1-Var10);
    array arr[*] _numeric_;
    do i = 1 to 11;
        if arr[i] = . then arr[i] = max(of arr[i]);
    end;
    drop i;
    put arr[2];
run;

In my solution, each row maximum works well. But each column maximum value was still missing.

But if I assign some exact number to each last column cell, the code works well. Like:

data findmax;
    set test;
    Var11 = max(of Var1-Var10);
    array arr[*] _numeric_;
    do i = 1 to 11;
        if arr[i] = . then arr[i] = 99;
    end;
    drop i;
    put arr[2];
run;

A minor difference of my code ( arr[ i ] = max(of arr[i]) vs. arr[ i ] = 99 ) makes such different output. Is there any reason in this case and how can I achieve the purpose.

Thanks, best regards.

Re: Find maximum value of each array variable and assign it to last row cell

KachiM — Wed, 07 Aug 2019 08:30:45 GMT

Try:

data test;
    input Var1-Var11;
cards;
0 1 2 3 4 5 6 7 8 3 .
9 0 1 2 3 4 5 6 7 8 .
8 9 0 1 2 3 4 5 6 7 .
;
run;


data findmax;
    set test;
    Var11 = max(of Var1-Var10);
run;

Re: Find maximum value of each array variable and assign it to last row cell

Shmuel — Wed, 07 Aug 2019 08:32:25 GMT

Pay attention, arr[i] is just one variable, then max(arr[i]) = arr[i] always.

May be you meant to code:

data findmax;
    set test;
    Var11 = max(of Var1-Var10);
    array arr[*] _numeric_;
    do i = 1 to 11;
        if arr[i] = . then arr[i] = var11; /* line changed */
    end;
    drop i;
    put arr[2];
run;

and do you realy want to assign the max value instead missing value, which is the lowest value ?

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 08:41:10 GMT

Thank you for your reminder. What I want is just like this:

0 1 2 3 4 5 6 7 8 9 9
9 0 1 2 3 4 5 6 7 8 9
8 9 0 1 2 3 4 5 6 7 9
9 9 2 3 4 5 6 7 8 9 9

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 08:43:23 GMT

Thank you for your reply.
Yes, as you say. The right dots are replaced in this way. But the bottom dots still need to be filled.

Re: Find maximum value of each array variable and assign it to last row cell

KachiM — Wed, 07 Aug 2019 08:45:49 GMT

I have changed a value in the first row to have max = 8;

Are you interested in getting the maximum of each column including the 11-th column?

Re: Find maximum value of each array variable and assign it to last row cell

Jagadishkatam — Wed, 07 Aug 2019 08:49:09 GMT

You may try the below approach

data test;
    input Var1-Var10 ;
cards;
0 1 2 3 4 5 6 7 8 9
9 0 1 2 3 4 5 6 7 8
8 9 0 1 2 3 4 5 6 7
;
run;

proc means data=test noprint;
var var1-var10;
output out=test2(where=(_stat_='MAX'));
run;

data want;
set test test2;
var11=max(of var1-var10);
drop _freq_ _stat_ _type_;
run;

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 08:59:09 GMT

Yes, including the 11-th column. And I forgot a requirement： Only data step allowed.

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 09:02:08 GMT

Hello, Jag. Thank you for your kindly help. The code works well. But if there any approach to solve it by Only data step? Thanks again.

Re: Find maximum value of each array variable and assign it to last row cell

Jagadishkatam — Wed, 07 Aug 2019 09:12:42 GMT

Yes it is possible with the below code, hope it helps

data test;
    input Var1-Var10 ;
cards;
0 1 2 3 4 5 6 7 8 9
9 0 1 2 3 4 5 6 7 8
8 9 0 1 2 3 4 5 6 7
;
run;

data want;
set test end=eof;
var11=max(of var1-var10);
output;
retain maxvar1-maxvar10;
array vars(10) var1-var10;
array maxvars(10) maxvar1-maxvar10;
do i = 1 to 10;
if vars(i)> maxvars(i) then maxvars(i)=vars(i);
vars(i)=maxvars(i);
end;
var11=max(of maxvar1-maxvar10);
if eof then output;
drop maxvar: i;
run;

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Wed, 07 Aug 2019 09:22:59 GMT

Thank you Jag. Your solution are very helpful. Thanks again.

And BTW, thanks for all participants' help.

Re: Find maximum value of each array variable and assign it to last row cell

novinosrin — Wed, 07 Aug 2019 12:50:29 GMT

Hello @Dominus Sorry, Too late to the party as we were sleeping in EDT time

FWIW


data test;
    input Var1-Var10 ;
cards;
0 1 2 3 4 5 6 7 8 9
9 0 1 2 3 4 5 6 7 8
8 9 0 1 2 3 4 5 6 7
;
run;

data want;
do _n_=1 by 1 until(z);
 set test end=z;
 array v var:;
 array t(3,10) _temporary_;
  do _i=1 to 10;
   t(_n_,_i)=v(_i);
   end;
  output;
end;
call missing(of v(*));
do _j=1 to 10;
 do _k=1 to 3;
    v(_j)=max(v(_j), t(_k,_j));
 end;
end;
output;
drop _:;
run;

Re: Find maximum value of each array variable and assign it to last row cell

novinosrin — Wed, 07 Aug 2019 15:23:21 GMT



data test;
    input Var1-Var10 ;
cards;
0 1 2 3 4 5 6 7 8 9
9 0 1 2 3 4 5 6 7 8
8 9 0 1 2 3 4 5 6 7
;
run;

data want;
do until(z);
 set test end=z;
 array v var:;
 array t(10) _temporary_ ;
 do _n_=1 to dim(v);
 t(_n_)=t(_n_) max v(_n_);
 end;
 output;
end;
call pokelong(peekclong(addrlong(t(1)),80),addrlong(v(1)),80);
output;
run;

Re: Find maximum value of each array variable and assign it to last row cell

Dominus — Thu, 08 Aug 2019 02:14:34 GMT

Hello @novinosrin, many thanks for your kindly help. These two solutions let me benefit a lot. And it's a long way for me to learn SAS.