Compute top 2 state and top 5 cities

[aashish_jain]

DATA A;
length state $20 city $30;
INPUT STATE$ CITY$ POP;
CARDS;
MAHA PUNE 200
MAHA SATARA 300
MAHA MUMB 250
MAHA NAGPUR 50
MAHA SOLAPUR 56
MAHA KOLAHPUR 88
GUJ GANDHINGR 20
GUJ AHMDABAD 40
GUJ PORBUNDR 55
GUJ SURAT 66
GUJ VADODRA 99
GUJ RAJKOT 100
UP LUCKNOW 98
UP AGRA 66
UP KANPUR 55
UP GZB 39
UP BAREILLY 22
UP VARANASI 66
;
RUN;

IF i have  a data like this and i want to calculate top 2 state based upon max population and along with its top 5 cities,how would i do it?

[novinosrin]

Hi @aashish_jain  Please take a look at PROC RANK. That should do what you are after.

Sir @PaigeMiller  happy? 🙂

[PaigeMiller]

@aashish_jain 

Would you please be so kind as to change the Subject of your post from "SAS PROGRAMMING" to a subject that actually describes the problem you are trying to solve? Thank you. This helps everyone with similar problems to find posts about it. If all posts had the subject line "SAS PROGRAMMING", this forum would be useless to most people.

[aashish_jain]

I believe this will be done in 2 steps, proc rank can be used,

But I tried first and last dot but only one solution is achievable not both.

[mkeintz]

You want

"IF i have a data like this and i want to calculate top 2 state based upon max population and along with its top 5 cities,how would i do it?"

By top 2 states, I presume you intend to total up population for all cities within each state, and take the 2 largest states.  And from there, you want the largest 5 cities in each of those states, right?   If so, proc rank won't work until you select the states.

Consider proc summary as a way to generate state totals in a summary data set.  Then choose the two largest totals and merge that with your city dataset based on the state identifier.  You could then run a proc rank, to produce city ranks within state, and filter out cities without a qualifying city rank.

BTW, what if you have ties in state totals or city populations?