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;
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;
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;
Well that was certainly easier than I was making it and a very fast reply, thank you so much!
data want;
set test;
by group;
if first.group then running_min=.;
running_min=min(running_min,amount);
retain running_min;
run;
Try
data want; set have; by group; current_min = ifn ( first.group, amount, min(amount,current_min) ); retain current_min; run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.