LIBNAME MYDATA '/folders/myfolders/data sources/' ; /*****************************************************/ /** ROUTINE.....: FIRST-BY-GROUP-ROWS **/ /** PURPOSE.....: Derive the first (min) row within **/ /** each by-group using a subquery. **/ /*****************************************************/ proc sql; create table first_bygroup_rows as select rating, title, 'FirstRow' as ByGroup from mydata.movies M1 where title = (select min(title) from mydata.movies M2 where M1.rating = M2.rating) order by rating, title; /*****************************************************/ /** ROUTINE.....: LAST-BY-GROUP-ROWS **/ /** PURPOSE.....: Derive the last (max) row within **/ /** each by-group using a subquery. **/ /*****************************************************/ create table last_bygroup_rows as select rating, title, 'LastRow' as ByGroup from mydata.movies M1 where title = (select max(title) from mydata.movies M2 where M1.rating = M2.rating) order by rating, title; /************************************************************/ /** ROUTINE.....: BETWEEN-BY-GROUP-ROWS **/ /** PURPOSE.....: Derive not the first (min) row and not **/ /** the last (max) row within each By-group. **/ /************************************************************/ create table between_bygroup_rows as select rating, title, min(title) as Min_Title, max(title) as Max_Title, 'BetweenRow' as ByGroup from mydata.movies group by rating having CALCULATED min_Title NOT = CALCULATED max_Title AND CALCULATED min_Title NOT = Title AND CALCULATED max_Title NOT = Title order by rating, title; /***********************************************************/ /** ROUTINE.....: CONCATENATE-FIRST-BETWEEN-LAST **/ /** PURPOSE.....: Concatenate the results from the first **/ /** (min) row, between rows, and last (max) **/ /** row within each by-group, and print. **/ /***********************************************************/ create table first_between_last_rows as select rating, title, bygroup from first_bygroup_rows UNION ALL select rating, title, bygroup from between_bygroup_rows UNION ALL select rating, title, bygroup from last_bygroup_rows; select * from first_between_last_rows; quit;