topic Re: Find Min and Max of five variables with min above zero for each row in SAS Programming

Find Min and Max of five variables with min above zero for each row

jbird — Tue, 09 Apr 2024 02:10:50 GMT

Hello,

I have a data set with 6 variables: ID, IR00, IR10, IR20, IR30, IR40. ID is an 8 digit numeric ID. IR00 through IR40 are each continous variable from 0 to 1. I need to find the min and max for each ID across the IR00 to IR40 variables. The min has to be above 0.

I have found the max using:

data new;

set old;

MAX_IR = max(of IR00 - IR40) ;

output;

keep ID IR00 IR10 IR20 IR30 IR40 MAX_IR;

run;

But I cannot figure out how to find the min value above zero. Any help would be appreciated.

Re: Find Min and Max of five variables with min above zero for each row

Patrick — Tue, 09 Apr 2024 03:50:04 GMT

Can't think of anything "shorthand". Below should work.

data new;
  set old;
  array vars{*} IR00 - IR40;
  do i=1 to dim(vars);
    if vars[i]>0 then min_ir=min(min_ir,vars[i]);
  end;

  MAX_IR = max(of IR00 - IR40);
  output;
  keep ID IR00 IR10 IR20 IR30 IR40 MAX_IR min_ir;
run;

Re: Find Min and Max of five variables with min above zero for each row

SASKiwi — Tue, 09 Apr 2024 03:45:12 GMT

Try this. The SMALLEST function ignores missing values:

data new;
  set old;
  MAX_IR = max(of IR00 - IR40);
  if IR00 = 0 then IR00 = .;
  if IR10 = 0 then IR10 = .;
  if IR20 = 0 then IR20 = .;
  if IR30 = 0 then IR30 = .;
  if IR40 = 0 then IR40 = .;
  MIN_IR = smallest(of IR00 - IR40);
run;

Re: Find Min and Max of five variables with min above zero for each row

Patrick — Tue, 09 Apr 2024 03:58:39 GMT

@SASKiwi Also the min() function skips missings but I believe the real "challenge" is: "The min has to be above 0. "

if IR00 <= 0 then IR00 = .;

Re: Find Min and Max of five variables with min above zero for each row

yabwon — Tue, 09 Apr 2024 09:46:45 GMT

With SAS Arrays?

data old;
  call streaminit(42);
  do id = 1 to 1e2;
    array ir IR00 IR10 IR20 IR30 IR40;
    do over IR;
      IR = rand("integer",0,9)/10;
    end;
  output;
  end;
run;

data new;
  set old;

  array IR IR:;
  do over IR;
               MAX_IR = max(MAX_IR,IR);
    if IR then MIN_IR = min(MIN_IR,IR);
  end;

  keep ID IR: M:; 
run; 
proc print;
run;

Bart

P.S. Just for fun

data new;
  set old;

  array IR IR:;
  do over IR;
    MAX_IR = MAX_IR<>IR;
    MIN_IR = min(MIN_IR,IR+(0=IR));
  end;

  keep ID IR: M:; 
run;

Re: Find Min and Max of five variables with min above zero for each row

yabwon — Tue, 09 Apr 2024 10:14:00 GMT

One more just for fun:

data new;
  set old;

  array IR IR:; /* make 0 to . */
  do over IR;
    IR = ifn(IR,IR,.);
  end;
  MAX_IR = max(of IR[*]);
  MIN_IR = min(of IR[*]);

  set old;

  keep ID IR: M:; 
run; 
proc print;
run;

Re: Find Min and Max of five variables with min above zero for each row

mkeintz — Wed, 10 Apr 2024 02:16:21 GMT

You may have to protect against all missing IR values or all zeroes. If so, then:

data want (drop=_:);
  set have ;
  if n(of IR:)>0 then max=max(of IR:);
  if max=0 then max=.;

  if max>0 then do _r=1 to n(of IR:) until(min>0);
    min=smallest(_r,of IR:);
  end;
run;

The "do ... UNTIL" using smallest is a way to avoid min=0.