BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JillChen0131
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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;

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
JillChen0131
Fluorite | Level 6

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
Diamond | Level 26

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

--
Paige Miller
JillChen0131
Fluorite | Level 6

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
Fluorite | Level 6

the category may not alphabetically.

ballardw
Super User

@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
Fluorite | Level 6
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
Jade | Level 19

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
Fluorite | Level 6

This works for me. Thanks.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1080 views
  • 0 likes
  • 4 in conversation