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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Havi
Obsidian | Level 7
Thanks RW9 - will try this and let you know.

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
  • 2 replies
  • 2417 views
  • 1 like
  • 2 in conversation