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 |
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.