DATA Step, Macro, Functions and more

Need Urgent Help again!! Appreciate it.

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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: 7,866

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

View solution in original post


All Replies
Contributor
Posts: 66

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: 66

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to 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;

Michelle
Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to mbuchecker

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

Super User
Posts: 7,866

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
Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to KurtBremser

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: 7,866

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
Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to KurtBremser
I greatly appreciate your help. Yes...I got it. again.Thank you so much.
Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to KurtBremser

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.

Posted in reply to KurtBremser
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.
Regular Contributor
Posts: 222

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=_Smiley Happy max(gdp)=maximum min(gdp)=minimum;

run;

 

Ahmed

 

Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to AhmedAl_Attar
Thanks a lot. This work too. I appreciate your time and help.
Super Contributor
Posts: 308

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: 7,866

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
Contributor
Posts: 37

Re: Need Urgent Help again!! Appreciate it.

Posted in reply to KurtBremser

Thank you so much.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 408 views
  • 12 likes
  • 6 in conversation