topic min & max within macro in SAS Programming

min & max within macro

capam — Mon, 29 Jan 2018 19:47:32 GMT

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
2801	1113	65405.85	65405.85
2801	1113	66931.5	66931.5
2801	1113	69289.32	69289.32
2801	1113	69479.6	69479.6
2801	1113	69479.6	69479.6
2801	1113	69479.6	69479.6
2801	1113	69479.6	69479.6
2801	1114	61879.68	61879.68
2801	1114	65404.52	65404.52
2801	1114	66928.74	66928.74
2801	1114	69270.52	69270.52
2801	1114	69460.8	69460.8
2801	1114	69460.8	69460.8
2801	1114	69460.8	69460.8
2801	1114	69460.8	69460.8

%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

Re: min & max within macro

Reeza — Mon, 29 Jan 2018 19:50:45 GMT

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;

Re: min & max within macro

PaigeMiller — Mon, 29 Jan 2018 19:53:54 GMT

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.

Re: min & max within macro

Astounding — Mon, 29 Jan 2018 19:58:54 GMT

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;

Re: min & max within macro

Reeza — Mon, 29 Jan 2018 19:56:28 GMT

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

Re: min & max within macro

capam — Mon, 29 Jan 2018 20:28:40 GMT

Great, but the range is not included. Can proc summary give a range?

Re: min & max within macro

PaigeMiller — Mon, 29 Jan 2018 20:29:26 GMT

@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.

Re: min & max within macro

capam — Mon, 29 Jan 2018 20:30:41 GMT

Thanks for the quick response and a great idea.