In the below code I'm trying to take the difference of min and max values of a variable that varies with RN and stat. The following code gives the output:
unit SORT_ORDER min_stat max_stat stat_dist
2801 | 1113 | 61881.27 | 61881.27 | 0 |
2801 | 1113 | 65405.85 | 65405.85 | 0 |
2801 | 1113 | 66931.5 | 66931.5 | 0 |
2801 | 1113 | 69289.32 | 69289.32 | 0 |
2801 | 1113 | 69479.6 | 69479.6 | 0 |
2801 | 1113 | 69479.6 | 69479.6 | 0 |
2801 | 1113 | 69479.6 | 69479.6 | 0 |
2801 | 1113 | 69479.6 | 69479.6 | 0 |
2801 | 1114 | 61879.68 | 61879.68 | 0 |
2801 | 1114 | 65404.52 | 65404.52 | 0 |
2801 | 1114 | 66928.74 | 66928.74 | 0 |
2801 | 1114 | 69270.52 | 69270.52 | 0 |
2801 | 1114 | 69460.8 | 69460.8 | 0 |
2801 | 1114 | 69460.8 | 69460.8 | 0 |
2801 | 1114 | 69460.8 | 69460.8 | 0 |
2801 | 1114 | 69460.8 | 69460.8 | 0 |
%macro add_dist(RN,stat);
data have;
set have;
if unit = &RN then
if SORT_ORDER = &stat then
do;
min_stat = min(STATISTIC_VAL);
max_stat = max(STATISTIC_VAL);
stat_dist = max(STATISTIC_VAL) - min(STATISTIC_VAL);
end;
run;
%mend add_dist;
%add_dist(2801,1113);
%add_dist(2801,1114);
The desired output is:
unit SORT_ORDER min_stat max_stat stat_dist
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1113 | 61881.27 | 69479.6 | 7598.334 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
2801 | 1114 | 61879.68 | 69460.8 | 7581.12 |
While I agree with Reeza and Paige about not needing macros, you do want to add the min and max to each observation. So you can either use SQL, or merge Reeza's results back into your data, or do it all in one step:
data want;
do until (last.sort_order);
set have;
by unit sort_order;
min_stat = min(min_stat, statistic_val);
max_stat = max(max_stat, statistic_val);
end;
stat_dist = max_stat - min_stat;
do until (last.sort_order);
set have;
by unit sort_order;
output;
end;
run;
Ok, why do you need a macro here?
proc means data=have noprint nway;
class unit sort_order;
var statistic_val;
output out=want min=min_stat max=max_stat range=stat_dist;
run;
What is RN?? This is not clear. Are the original variable min_stat and max_stat always equal as in your example?
Other than that, everything you are doing can be done much simpler and without macros by using PROC SUMMARY and then merge the results back into your original dataset.
UNTESTED CODE
proc summary nway data=have;
class unit sort_order;
var min_stat max_stat;
output out=results min(min_stat)= max(max_stat)=;
run;
No sorting needed, no macros needed.
@capam wrote:
Great, but the range is not included. Can proc summary give a range?
Yes, and also range is one of the simplest statistics to compute, you could compute it yourself.
While I agree with Reeza and Paige about not needing macros, you do want to add the min and max to each observation. So you can either use SQL, or merge Reeza's results back into your data, or do it all in one step:
data want;
do until (last.sort_order);
set have;
by unit sort_order;
min_stat = min(min_stat, statistic_val);
max_stat = max(max_stat, statistic_val);
end;
stat_dist = max_stat - min_stat;
do until (last.sort_order);
set have;
by unit sort_order;
output;
end;
run;
This may be a useful example, it covers how to add statistics to your data set. Note that RANGE is the statistic name for the Max-Min statistic.
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.