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

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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

View solution in original post

17 REPLIES 17
mbuchecker
Quartz | Level 8

(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
mbuchecker
Quartz | Level 8

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

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

Kurt_Bremser
Super User

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

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
Super User

@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
Obsidian | Level 7
I greatly appreciate your help. Yes...I got it. again.Thank you so much.
Cathy
Obsidian | Level 7

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

Cathy
Obsidian | Level 7
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
Rhodochrosite | Level 12

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
Obsidian | Level 7
Thanks a lot. This work too. I appreciate your time and help.
Loko
Barite | Level 11

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
Super User

@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
Obsidian | Level 7

Thank you so much.

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
  • 17 replies
  • 1422 views
  • 12 likes
  • 6 in conversation