DATA Step, Macro, Functions and more

How to find min max for a range

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to find min max for a range

Hello People

 

Please help. I have an input dataset with a range on min and max values per grouping. I would like to get the first and last value per range. See below for example:

 

INPUT:

RANGE MIN MAX
High 1 10
High 11 20
Medium 21 29
Medium 30 69
Medium 70 99
Medium 100 299
Medium 300 499
Medium 500 1000
Low 1001 1499
Low 1500 2000
Very Low 2001 6000
Very Low 6001 9000

 

DESIRED OUTPUT:

 

RANGE MIN MAX
High 1 20
Medium 21 1000
Low 1001 2000
Very low 2001 9000

 


Accepted Solutions
Solution
‎10-01-2015 08:46 AM
Super User
Super User
Posts: 7,401

Re: How to find min max for a range

[ Edited ]

Hi,

 

data have;
  input RANGE $ MIN MAX;
datalines;
High 1 10
High 11 20
Medium 21 29
Medium 30 69
Medium 70 99
Medium 100 299
Medium 300 499
Medium 500 1000
Low 1001 1499
Low 1500 2000
Very_Low 2001 6000
Very_Low 6001 9000
;
run;

proc sort data=have;
  by range min;
run;

data want;
  set have;
  by range;
  retain min_val max_val;
  if first.range then min_val=min;
  if last.range then do;
    max_val=max;
    output;
  end;
run;

 

To add, you can also do:

proc sql;
  create table WANT as
  select RANGE,
            min(MIN) as MIN,
            max(MAX) as MAX
  from HAVE
  group by RANGE;
quit;

View solution in original post


All Replies
Solution
‎10-01-2015 08:46 AM
Super User
Super User
Posts: 7,401

Re: How to find min max for a range

[ Edited ]

Hi,

 

data have;
  input RANGE $ MIN MAX;
datalines;
High 1 10
High 11 20
Medium 21 29
Medium 30 69
Medium 70 99
Medium 100 299
Medium 300 499
Medium 500 1000
Low 1001 1499
Low 1500 2000
Very_Low 2001 6000
Very_Low 6001 9000
;
run;

proc sort data=have;
  by range min;
run;

data want;
  set have;
  by range;
  retain min_val max_val;
  if first.range then min_val=min;
  if last.range then do;
    max_val=max;
    output;
  end;
run;

 

To add, you can also do:

proc sql;
  create table WANT as
  select RANGE,
            min(MIN) as MIN,
            max(MAX) as MAX
  from HAVE
  group by RANGE;
quit;

Contributor
Posts: 36

Re: How to find min max for a range

Thanks RW9 - will try this and let you know.
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 231 views
  • 1 like
  • 2 in conversation