topic Re: Assign order variable in SAS Programming

Assign order variable

JillChen0131 — Mon, 04 Apr 2022 16:41:47 GMT

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

dd 3

Desired output:

Category Count Order

aa 10 1

bb 9 2

cc 8 3

dd 3 4

6 5

Re: Assign order variable

PaigeMiller — Mon, 04 Apr 2022 16:47:49 GMT

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?

Re: Assign order variable

JillChen0131 — Mon, 04 Apr 2022 16:50:23 GMT

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.

Re: Assign order variable

PaigeMiller — Mon, 04 Apr 2022 16:53:47 GMT

@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.

Re: Assign order variable

JillChen0131 — Mon, 04 Apr 2022 17:04:22 GMT

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.

Re: Assign order variable

JillChen0131 — Mon, 04 Apr 2022 17:05:48 GMT

the category may not alphabetically.

Re: Assign order variable

FreelanceReinh — Mon, 04 Apr 2022 17:19:01 GMT

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;

Re: Assign order variable

ballardw — Mon, 04 Apr 2022 17:31:55 GMT

@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.

Re: Assign order variable

JillChen0131 — Mon, 04 Apr 2022 17:45:36 GMT

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.

Re: Assign order variable

JillChen0131 — Mon, 04 Apr 2022 17:50:26 GMT

This works for me. Thanks.