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
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;
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?
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.
@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.
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.
the category may not alphabetically.
@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.
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;
This works for me. Thanks.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.