Need Urgent Help again!! Appreciate it.

[Cathy]

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.

[mbuchecker]

(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;

[mbuchecker]

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;

[Cathy]

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

[Kurt_Bremser]

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;

[Cathy]

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.

[Kurt_Bremser · Accepted solution]

---

@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

[Cathy]

I greatly appreciate your help. Yes...I got it. again.Thank you so much.

[Cathy]

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

[Cathy]

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.

[AhmedAl_Attar]

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

[Cathy]

Thanks a lot. This work too. I appreciate your time and help.

[Loko]

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;

[Kurt_Bremser]

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

[Cathy]

Thank you so much.