topic Re: Keep a running lowest value by group in SAS Programming

Keep a running lowest value by group

lukesirakos — Fri, 18 Sep 2020 21:22:48 GMT

I am trying to write some code that would go by group and row by row and determine if at each row if the current value is the lowest in the group. If so it would keep that value until it either reaches a lower value or gets to the next group. I have tried various retain and lags with if statements but I can't seem to come to the correct way of doing this. Any help is greatly appreciated.

An example of data is below where running_min is the outcome I am looking for:

group	amount	running_min
a	10	10
a	9	9
a	9	9
a	8	8
a	7	7
a	6	6
a	5	5
a	9	5
a	10	5
b	100	100
b	20	20
b	35	20
b	45	20
b	10	10
b	50	10

Below is the test data I am working with

/* Code for test data */
data test;
input group $ amount;
cards;
a 10
a 9
a 9
a 8
a 7
a 6
a 5
a 9
a 10
b 100
b 20
b 35
b 45
b 10
b 50
;
run;

Re: Keep a running lowest value by group

novinosrin — Fri, 18 Sep 2020 21:27:12 GMT


data have;
input group	$ amount;*	running_min;
cards;
a	10	10
a	9	9
a	9	9
a	8	8
a	7	7
a	6	6
a	5	5
a	9	5
a	10	5
b	100	100
b	20	20
b	35	20
b	45	20
b	10	10
b	50	10
;

data want;
 do until(last.group);
  set have;
  by group;
  running_min=min(running_min,amount);
  output;
 end;
run;

Re: Keep a running lowest value by group

lukesirakos — Fri, 18 Sep 2020 21:31:48 GMT

Well that was certainly easier than I was making it and a very fast reply, thank you so much!

Re: Keep a running lowest value by group

PeterClemmensen — Sat, 19 Sep 2020 06:57:53 GMT

data want;
   set test;
   by group;
   if first.group then running_min=.;
   running_min=min(running_min,amount);
   retain running_min;
run;

Re: Keep a running lowest value by group

RichardDeVen — Sat, 19 Sep 2020 11:27:36 GMT

Try

data want;
  set have;
  by group;
  current_min = ifn ( first.group, amount, min(amount,current_min) );
  retain current_min;  
run;

Re: Keep a running lowest value by group

lukesirakos — Mon, 21 Sep 2020 15:17:48 GMT

Ah this is brilliant and the perfect solution for what I wanted to do after this step was figured out. I can't believe how close I was and how simple it ended up being. Thank you so much!