Desktop productivity for business analysts and programmers

How to pick top 4 populated Cities

Reply
Contributor
Posts: 21

How to pick top 4 populated Cities

Hi,

I have a dataset with 3 columns  state, city,& population which has 50 states with respective cities and populations. I want 50 different outputs with top 4 populated cities.

Thanks in advance

Contributor
Posts: 29

Re: How to pick top 4 populated Cities

Posted in reply to rkolupoti9001

/*Set up the initial data set*/
data have ;
input state $
      city  $
      pop     ;
datalines ;
AA AA 123
AA BB  46
AA CC  23
AA DD   8
AA EE 267
AA FF  19
BB GG 249
BB HH  64
BB II  44
BB JJ  72
BB KK 411
BB LL  55
;

proc sort data=have ; by state descending pop ; run ;

/*Keep the top 4 cities by population*/
data have2 (drop=count) ;
set  have ;
by   state descending pop ;
if   first.state then count = 0 ;
count + 1 ;
if count le 4 ;
run ;

/*create macro variables with the state names*/
proc sql noprint;
select count(state) into :nost from (select distinct state from have2) ;
quit ;
%let nost = &nost ;
proc sql noprint;
select distinct state into :stvar1 - :stvar&nost from have2 ;
quit ;

/*Output a data set for each state*/
%macro loopoutput() ;
%do i = 1 %to &nost ;
    data &&stvar&i ;
    set  have2 ;
    where state = "&&stvar&i" ;
    run ;
%end ;
%mend loopoutput ;
%loopoutput ;

Ask a Question
Discussion stats
  • 1 reply
  • 278 views
  • 0 likes
  • 2 in conversation