Solved
Contributor
Posts: 37

# Need Urgent Help again!! Appreciate it.

How can I get the city of smallest GDP and largest GDP  in each region of following example data set;

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
;

I would like to have result table with city, region, smallest and largest. Thanks a lot again. I appreciate it.

Accepted Solutions
Solution
‎04-06-2016 10:44 AM
Super User
Posts: 10,279

## Re: Need Urgent Help again!! Appreciate it.

Cathy wrote:

It shows like that in the log :"The variable city_large in the DROP, KEEP, or RENAME list has never been referenced.."

Very close the table I want. but in the table it shows only smallest county and smallest number.

Hmm

``````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;
set ex;
by region;
retain city_small gdp_small;
keep region city_small gdp_small city_large gdp_large;
if first.region
then do;
city_small = city;
gdp_small = gdp;
end;
if last.region
then do;
city_large = city;
gdp_large = gdp;
output;
end;
run;``````

show this log for the second datastep:

```32         data want;
33         set ex;
34         by region;
35         retain city_small gdp_small;
36         keep region city_small gdp_small city_large gdp_large;
37         if first.region
38         then do;
39           city_small = city;
40           gdp_small = gdp;
41         end;
42         if last.region
43         then do;
44           city_large = city;
45           gdp_large = gdp;
46           output;
47         end;
48         run;

NOTE: There were 6 observations read from the data set WORK.EX.
NOTE: The data set WORK.WANT has 2 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time           0.01 seconds
cpu time            0.00 seconds```

and PROC PRINT gives me this:

```                                                          city_     gdp_    city_     gdp_
Obs    Region    small    small    large    large

1       A       City4     8.5     City6     10.5
2       E       City3     5.5     City2     12.5
```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Contributor
Posts: 67

## Re: Need Urgent Help again!! Appreciate it.

[ Edited ]

(whoops, didn't see that you wanted the city. I'll work on that)

Try

proc sql;
select region, min(gdp) as mininum, max(gdp) as maximum
from ex
group by region;

Michelle
Contributor
Posts: 67

## Re: Need Urgent Help again!! Appreciate it.

alright, not pretty but:

proc sort data=ex out=sort;
by Region descending gdp;
data max;
set sort;
by region descending gdp;
type='Maximum';
if first.region;
run;
proc sort data=ex out=sort;
by Region gdp;
data min;
set sort;
by region gdp;
type='Minimum';
if first.region;
run;
data combine;
set max min;
run;

Michelle
Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

Thanks a lot for your time and help. It works too. I greatly appreciate it.

Super User
Posts: 10,279

## Re: Need Urgent Help again!! Appreciate it.

Try this

``````proc sort data=ex;
by region gdp;
run;

data want;
set ex;
by region;
retain city_small gdp_small;
keep region city_small gdp_small city_large gdp_large;
if first.region
then do;
city_small = city;
gdp_small = gdp;
end;
if last.region
then do;
city_large = city;
gdp_large = gdp;
output;
end;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

It shows like that in the log :"The variable city_large in the DROP, KEEP, or RENAME list has never been referenced.."

Very close the table I want. but in the table it shows only smallest county and smallest number.

Solution
‎04-06-2016 10:44 AM
Super User
Posts: 10,279

## Re: Need Urgent Help again!! Appreciate it.

Cathy wrote:

It shows like that in the log :"The variable city_large in the DROP, KEEP, or RENAME list has never been referenced.."

Very close the table I want. but in the table it shows only smallest county and smallest number.

Hmm

``````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;
set ex;
by region;
retain city_small gdp_small;
keep region city_small gdp_small city_large gdp_large;
if first.region
then do;
city_small = city;
gdp_small = gdp;
end;
if last.region
then do;
city_large = city;
gdp_large = gdp;
output;
end;
run;``````

show this log for the second datastep:

```32         data want;
33         set ex;
34         by region;
35         retain city_small gdp_small;
36         keep region city_small gdp_small city_large gdp_large;
37         if first.region
38         then do;
39           city_small = city;
40           gdp_small = gdp;
41         end;
42         if last.region
43         then do;
44           city_large = city;
45           gdp_large = gdp;
46           output;
47         end;
48         run;

NOTE: There were 6 observations read from the data set WORK.EX.
NOTE: The data set WORK.WANT has 2 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time           0.01 seconds
cpu time            0.00 seconds```

and PROC PRINT gives me this:

```                                                          city_     gdp_    city_     gdp_
Obs    Region    small    small    large    large

1       A       City4     8.5     City6     10.5
2       E       City3     5.5     City2     12.5
```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

I greatly appreciate your help. Yes...I got it. again.Thank you so much.
Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

Sorry...I got it...I was wrong in typing. Thanks you so much. I greatly appreciated it.

Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

If I want to get two largest gdp by city and two smallest gdp by city in one data set, how can I do that? Please help me again. Thanks.
Super Contributor
Posts: 282

## Re: Need Urgent Help again!! Appreciate it.

Proc Summary / Proc means would do the trick too.

Proc Summary data=ex nway;

class city region;

var gdp;

output out=work.results(DROP=_ max(gdp)=maximum min(gdp)=minimum;

run;

Ahmed

Contributor
Posts: 37

## Re: Need Urgent Help again!! Appreciate it.

Thanks a lot. This work too. I appreciate your time and help.
Super Contributor
Posts: 319

## Re: Need Urgent Help again!! Appreciate it.

proc means data=ex noprint nway;
class Region;
var GDP;
output out=want min(GDP)=min_GDP max(GDP)=max_GDP
idgroup (max(GDP)  out (City)=BestCity);
run;

Super User
Posts: 10,279

## Re: Need Urgent Help again!! Appreciate it.

@Loko has a nice solution, i would just do some cosmetics and expansion:

``````proc means data=ex noprint nway;
class Region;
var GDP;
output
out=want1 (drop=_:)
min(GDP)=min_GDP max(GDP)=max_GDP
idgroup (min(GDP) out (City) = LeastCity)
idgroup (max(GDP)  out (City)=BestCity)
;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37