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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

 

 

 

 

View solution in original post

21 REPLIES 21
jklaverstijn
Rhodochrosite | Level 12

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.

Cathy
Obsidian | Level 7

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

jklaverstijn
Rhodochrosite | Level 12

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;
Cathy
Obsidian | Level 7

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;

 

jklaverstijn
Rhodochrosite | Level 12

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.

Cathy
Obsidian | Level 7

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?

Reeza
Super User

@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 🙂

 

 

FreelanceReinh
Jade | Level 19

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

Cathy
Obsidian | Level 7

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

FreelanceReinh
Jade | Level 19

@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;
FreelanceReinh
Jade | Level 19

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;

 

Cathy
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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;

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
  • 21 replies
  • 1219 views
  • 13 likes
  • 6 in conversation