min & max within macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

min & max within macro

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

2801111361881.2761881.270
2801111365405.8565405.850
2801111366931.566931.50
2801111369289.3269289.320
2801111369479.669479.60
2801111369479.669479.60
2801111369479.669479.60
2801111369479.669479.60
2801111461879.6861879.680
2801111465404.5265404.520
2801111466928.7466928.740
2801111469270.5269270.520
2801111469460.869460.80
2801111469460.869460.80
2801111469460.869460.80
2801111469460.869460.80

 

%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

2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111361881.2769479.67598.334
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12
2801111461879.6869460.87581.12

Accepted Solutions
Solution
‎01-29-2018 03:46 PM
Super User
Posts: 6,785

Re: min & max within macro

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 23,778

Re: min & max within macro

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;
Respected Advisor
Posts: 3,066

Re: min & max within macro

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. 

--
Paige Miller
Frequent Contributor
Posts: 77

Re: min & max within macro

Posted in reply to PaigeMiller
Great, but the range is not included. Can proc summary give a range?
Respected Advisor
Posts: 3,066

Re: min & max within macro


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.

--
Paige Miller
Solution
‎01-29-2018 03:46 PM
Super User
Posts: 6,785

Re: min & max within macro

[ Edited ]

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;

Frequent Contributor
Posts: 77

Re: min & max within macro

Posted in reply to Astounding
Thanks for the quick response and a great idea.
Super User
Posts: 23,778

Re: min & max within macro

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 148 views
  • 4 likes
  • 4 in conversation