Assign order variable

[JillChen0131]

I have a dataset like as below, I need to assign the order based on descending count order to this dataset, when the category is missing, it should be the last in order. 

Category           Count

    aa                    10

    bb                      9

    cc                      8

                              6

    dd                      3

Desired output:

Category           Count         Order

    aa                    10                1

    bb                      9                2

    cc                      8                3

    dd                      3                4

                              6                5

[PaigeMiller]

Replace the missing value of CATEGORY with a text string not likely to appear in real data, such as "ZZZZZZ". Sort the data by CATEGORY. Assign the value of ORDER. Then replace "ZZZZZZ" with missing.

Which brings up the question, why would you need a specific sort order variable after change missing to "ZZZZZZ"? Why not just use CATEGORY at this point?

[JillChen0131]

It is a good way to reorder. 

We pay more attention to the count number to see it belongs to which category. For those missing category, no study meaning for us, so keep it in the last. 

[PaigeMiller]

---

@JillChen0131 wrote:

It is a good way to reorder. 

We pay more attention to the count number to see it belongs to which category. For those missing category, no study meaning for us, so keep it in the last. 

---

I find this confusing. Using a category number is fine if you want to know which category is 3rd, but you didn't say that. And since the sorting is alphabetical, I'm not sure I see value in knowing that a particular category is 3rd alphabetically.

[JillChen0131]

I found assigning the missing category to "zzzzzz" is not working. I would need to sort the dataset by descending count first, based on that, all other categories need to go above the missing one, even the count is less than the missing one. 

[JillChen0131]

the category may not alphabetically.

[ballardw]

---

@JillChen0131 wrote:

the category may not alphabetically.

---

So how do we know what "order" to apply to anything? Do you have another data set somewhere with a specified order??

Hint: your example data must be representative of the actual data is use. If you imply an order of values, such as your values of aa, bb, cc then we have to assume that the values are in order. Especially since the request involves ordering something and the only thing in your "want" is that except for the missing value they are in order.

[JillChen0131]

Order is based on the count. The missing category goes to the last whatever the count is, any other category above it should be ordered by descending count.

[FreelanceReinh · Accepted solution]

Hello @JillChen0131,

You can create the order with a PROC SQL view and then assign the observation number to variable Order in a DATA step:

data have;
input Category $ Count;
cards;
aa 10
bb 9
cc 8
.  6
dd 3
;

proc sql;
create view _tmp as
select * from have
order by missing(category), count desc, category;
quit;

data want;
set _tmp;
Order=_n_;
run;

[JillChen0131]

This works for me. Thanks.