DATA Step, Macro, Functions and more

Top and bottom 2 by city using a data step

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Top and bottom 2 by city using a data step

[ Edited ]

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. 


Accepted Solutions
Solution
‎04-12-2016 10:26 AM
Super User
Posts: 19,855

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

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 Smiley Happy

 

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;

 

 

 

 

View solution in original post


All Replies
Super Contributor
Posts: 441

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

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.

Contributor
Posts: 37

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

[ Edited ]
Posted in reply to jklaverstijn

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

Super Contributor
Posts: 441

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

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;
Contributor
Posts: 37

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

Posted in reply to jklaverstijn

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;

 

Super Contributor
Posts: 441

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

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.

Contributor
Posts: 37

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

Posted in reply to jklaverstijn

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?

Super User
Posts: 19,855

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


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 Smiley Happy

 

 

Trusted Advisor
Posts: 1,118

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

@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).

Contributor
Posts: 37

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

Posted in reply to FreelanceReinhard

@FreelanceReinhard 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.

Trusted Advisor
Posts: 1,118

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

@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;
Contributor
Posts: 37

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

Thank you.

Trusted Advisor
Posts: 1,118

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

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;

 

Contributor
Posts: 37

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

Posted in reply to FreelanceReinhard

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

Respected Advisor
Posts: 3,156

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

I would opt for @FreelanceReinhard'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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 434 views
  • 13 likes
  • 6 in conversation