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
/*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 ;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.