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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.