How to pick top 4 populated Cities

[rkolupoti9001]

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

[FatCaptain]

/*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 ;