topic Re: This post is part of the post "Need Urgent Help again!! Appreciate it." in SAS Programming

Top and bottom 2 by city using a data step

Cathy — Wed, 06 Apr 2016 16:49:08 GMT

This post is part of the post "Need Urgent Help again!! Appreciate it."

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;

If want to get two largest gdp by city and two smallest gdp by city in one data set, how can I do it? Please advise me again. Thanks. I am a beginner to SAS and these problems takes me for 2 to 3 days to work it, but still I don't get it. I appreciate this forum and everyone's help.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

jklaverstijn — Wed, 06 Apr 2016 16:28:57 GMT

Many ways to do this. This is quick and dirty (two passes of the data). Secret is in the OUTOBS option:

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 sql outobs=2;
create table largest2 as

select city, gdp
from ex
order by gdp desc;

create table smallest2 as
select city, gdp
from ex
order by gdp;

quit;

data all;
set largest2 smallest2;
run;

proc print data=largest2;
run;

proc print data=smallest2;
run;

proc print data=all;
run;

Hope this helps,

- Jan.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 16:33:15 GMT

anyothe ways not using proc sql? if so, please instruct me. Thanks a lot for your help, too.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

jklaverstijn — Wed, 06 Apr 2016 16:48:18 GMT

Shure why not.

proc sort data=ex;
    by gdp;
run;

data want;
    if 0 then set ex nobs=n;
    do i=1, 2, n-1, n;
        set ex point=i;
        output;
    end;
    stop;
run;

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

FreelanceReinh — Wed, 06 Apr 2016 16:52:35 GMT

PROC SUMMARY/PROC MEANS (as suggested by Loko and KurtBremser in the other thread) allows for the "n largest/smallest" values as well:

proc summary data=ex nway;
class region;
var GDP;
output out=want(drop=_:) idgrp(max(GDP) out[2] (GDP city)=highGDP city_highGDP)
                         idgrp(min(GDP) out[2] (GDP city)=lowGDP  city_lowGDP);
run;

Re: Top and bottom 2 by city using a data step

Haikuo — Wed, 06 Apr 2016 16:56:01 GMT

I would opt for @FreelanceReinh's solution, but FWIW, here is a data step solution:

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 region gdp;
run;

data want;
	do _s=1 by 1 until (last.region);
		set ex;
		by region gdp;

		if _s=1 then
			smallest_city=city;

		if _s=2 then
			second_smallest_city=city;
	end;

	do _l=1 by 1 until (last.region);
		set ex;
		by region gdp;

		if _l=_s then
			largest_city=city;

		if _l=_s-1 then
			second_largest_city=city;
	end;

	drop _: city gdp;
run;

Re: Top and bottom 2 by city using a data step

Reeza — Wed, 06 Apr 2016 16:58:20 GMT

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;

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 16:58:45 GMT

This is good one, but why you need to have n-1 and I don't understand the codes. Thanks. Would you mind to explain me?

 if 0 then set ex nobs=n;
    do i=1, 2, n-1, n;
        set ex point=i;

Re: Top and bottom 2 by city using a data step

Cathy — Wed, 06 Apr 2016 17:01:30 GMT

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.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

jklaverstijn — Wed, 06 Apr 2016 17:18:07 GMT

I don't mind to explain. Consider n is the number of rows in the dataset sorted by ascending gdp. So rows 1 and 2 are the cities with the lowest gdp. Rows n-1 and n are the cities with the highest gdp.

The point= options allows access by rownumber. The statement with nobs= stores the number of rows in variable n but never actually gets executed due to the "if 0". This approach is very efficient. If you don't count the sort.

Hope this explains my code.

Regards,

- Jan.

Re: Top and bottom 2 by city using a data step

Cathy — Wed, 06 Apr 2016 17:23:11 GMT

Thank you Haikuo I appreciate for your help too.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 17:27:42 GMT

Thank you again. So if I have large data set, let's say I want 10 lowest and 10 largest, so I need to write from rows 1 to 10?

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 17:28:45 GMT

Thank you very much for you input too. I appreciate it. I like your codes as well.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Reeza — Wed, 06 Apr 2016 17:29:33 GMT

@Cathy wrote:

Thank you again. So if I have large data set, let's say I want 10 lowest and 10 largest, so I need to write from rows 1 to 10?

You have a variety of solutions provided, in your response we can't see which one you're referring to.

In mine, you can change the 2 to 10 and _N_-1 to _n_ - 9 and you'll be fine.

The Proc Summary solution also scales well, change 2 to 10.

Not all of the others do 🙂

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

FreelanceReinh — Wed, 06 Apr 2016 18:18:23 GMT

@Cathy: Also, please note that some of the suggested solutions (including mine) select the two smallest and largest GDPs in each region, as this was specified in your earlier post, whereas others select the overall extreme values from the input dataset (maybe correctly, because you didn't write "in each region," but "by city" in the initial post of the current thread).

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 19:13:54 GMT

Thank you.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

Cathy — Wed, 06 Apr 2016 19:16:47 GMT

@FreelanceReinh I want 2 smallest and largest only by city for this thread. yes, I needed to do by region for previous one. Thanks for clarification.

Re: This post is part of the post "Need Urgent Help again!! Appreciate it."

FreelanceReinh — Wed, 06 Apr 2016 19:21:48 GMT

@Cathy: Thanks for the clarification. In this case, you can omit the NWAY option and CLASS statement in the PROC SUMMARY approach:

proc summary data=ex;
var GDP;
output out=want(drop=_:) idgrp(max(GDP) out[2] (GDP city)=highGDP city_highGDP)
                         idgrp(min(GDP) out[2] (GDP city)=lowGDP  city_lowGDP);
run;

Re: Top and bottom 2 by city using a data step

Ksharp — Thu, 07 Apr 2016 01:40:56 GMT

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;

Re: Top and bottom 2 by city using a data step

FreelanceReinh — Thu, 07 Apr 2016 10:37:47 GMT

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.