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.
@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
(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;
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;
Thanks a lot for your time and help. It works too. I greatly 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;
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.
@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
Sorry...I got it...I was wrong in typing. Thanks you so much. I greatly appreciated 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
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;
@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;
Thank you so much.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.