Top and bottom 2 by city using a data step

[Cathy]

Thank you Haikuo I appreciate for your help too.

[Reeza · Accepted solution]

Hi Cathy,

Welcome to programming in SAS. Here's a couple of hints for getting the appropriate response.

Try and include a descriptive subject line for your problem. Saying you need immediate help isn't helpful for describing your problem. Everyone here has something that's an issue and that's why they're posting. 

It is good to link to your previous question if you've already asked it somewhere and the solution doesn't work for you.

I've modified your title here. 

Your question provides sample data, but it doesn't show what you want the output to look like so we are guessing a bit. It helps if you provide sample output and input 🙂

You want the top 2 and bottom 2 from your dataset.

The process is then:

A very simple way, and easily understood way is to:

Sort ascending, and take the first two as smallest.

Sort descending, and take the first two as largest

Combine datasets.

You can use the automatic variable _n_ as a pseudo row counter, and output explicitly tells SAS to keep those records.

data ex;
input City $ Region $ GDP;
cards;
City1 E 11.1
City2 E 12.5
City3 E 5.5
City4 A 8.5
City5 A 9.5
City6 A 10.5
;
run;

proc sort data=ex;
by gdp;
run;

data smallest;
set ex;
if _n_ <= 2 then output;
run;

proc sort data=ex;
by descending gdp;
run;

data largest;
set ex;
if _n_ <=2 then output;
run;

data want;
set smallest largest;
run;

 A slightly more complex method is to use the NOBS variable  after you sort it. This is an automatic variable that allows you to know the number of observations in a dataset. Then in one pass you can take the top 2 (if _N_ <=2) and bottom two, (if _n_ >= NOBS-1)

data ex;
input City $ Region $ GDP;
cards;
City1 E 11.1
City2 E 12.5
City3 E 5.5
City4 A 8.5
City5 A 9.5
City6 A 10.5
;
run;

proc sort data=ex;
by gdp;
run;

data want;
set ex nobs=count; *store the nobs into a variable called count;

if _n_ <= 2 then output;

if _n_ >= count-1 then output;

run;

[Cathy]

Thanks a lot, I joined and registered the forum yesterday and I did not know how to post it. Thank you for your suggestion and correcting me.I appreciate it. 

[Ksharp]

Use proc univariate :




ods select none;
ods output ExtremeValues=want;
proc univariate data=sashelp.class nextrval=2 ;
class sex;
var age;
run;
ods select all;

[FreelanceReinh]

Good idea (as always) from @Ksharp!

I'd suggest a slight modification of his PROC UNIVARIATE approach:

ods select none;
ods output ExtremeObs=want;
proc univariate data=ex nextrobs=2;
*class region; /* commented out for overall extremes */
id city;
var gdp;
run;
ods select all;

With ExtremeObs and nextrobs you obtain the n (here: n=2) highest/lowest observations, whereas ExtremeValues and nextrval request the n highest/lowest values. This means, if, for example, two cities shared the top rank with equal GDP values (say, GDP=15), followed by a city with GDP=13, the above step would yield the two GDP=15 cities and would not include GDP=13 (as the second highest value). The ID statement includes variables City_Low and City_High in the WANT dataset to identify the cities with the extreme GDP values.

[Cathy]

@FreelanceReinh Thanks for your clarification and modification too.

[Cathy]

Thank you too. I appreciate it.