How to pick top 4 populated Cities

rkolupoti9001 — Thu, 13 Aug 2015 16:49:31 GMT

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

Re: How to pick top 4 populated Cities

FatCaptain — Thu, 10 Sep 2015 10:11:59 GMT

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

topic Re: How to pick top 4 populated Cities in SAS Enterprise Guide

How to pick top 4 populated Cities

Re: How to pick top 4 populated Cities