topic Re: Need Urgent Help again!! Appreciate it. in SAS Programming

Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:18:02 GMT

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.

Re: Need Urgent Help again!! Appreciate it.

mbuchecker — Wed, 06 Apr 2016 14:24:15 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Kurt_Bremser — Wed, 06 Apr 2016 14:25:07 GMT

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;

Re: Need Urgent Help again!! Appreciate it.

AhmedAl_Attar — Wed, 06 Apr 2016 14:28:34 GMT

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

Re: Need Urgent Help again!! Appreciate it.

mbuchecker — Wed, 06 Apr 2016 14:29:41 GMT

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;

Re: Need Urgent Help again!! Appreciate it.

Loko — Wed, 06 Apr 2016 14:31:12 GMT

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;

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:36:24 GMT

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.

Re: Need Urgent Help again!! Appreciate it.

Kurt_Bremser — Wed, 06 Apr 2016 14:37:02 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:39:22 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Astounding — Wed, 06 Apr 2016 14:40:18 GMT

The way I interpret this, you would need two CITY values (one for the smallest GDP, one for the largest). Here's an approach:

proc sort data=have;

by region gdp;

run;

data want;

set have;

by region;

if first.region then do;

type='Smallest';

output;

end;

if last.region then do;

type='Largest';

output;

end;

run;

Re: Need Urgent Help again!! Appreciate it.

Kurt_Bremser — Wed, 06 Apr 2016 14:41:20 GMT

@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

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:45:25 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:46:01 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:46:21 GMT

Thank you so much.

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:46:50 GMT

Thanks a lot for your help and time. I appreciate it.

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:47:35 GMT

Thanks a lot for your help and time again. I greatly appreciate it.

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 14:51:54 GMT

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

Re: Need Urgent Help again!! Appreciate it.

Cathy — Wed, 06 Apr 2016 16:06:23 GMT

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.