BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
capam
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Reeza
Super User

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;
PaigeMiller
Diamond | Level 26

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
capam
Pyrite | Level 9
Great, but the range is not included. Can proc summary give a range?
PaigeMiller
Diamond | Level 26

@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
Astounding
PROC Star

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;

capam
Pyrite | Level 9
Thanks for the quick response and a great idea.
Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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