topic Re: Order rows by user defined criteria in SAS Programming

Order rows by user defined criteria

Ronein — Fri, 28 Jan 2022 14:17:16 GMT

Hello

In real life I have a data set in Long structure where each customer has multiple rows.

In each row there is information on explanatory variables from regression model.

I want to order the rows in the way that for each customer there will be the following order or rows

Wealth
Cycle
Educ
Age
Inter

What is the way to do it please?

The order is not by alphabetical order (it is by user defined criteria)

Data have;
input category $;
cards;
Inter
Age
Educ
Wealth
Cycle
;
Run;


Data wanted;
input category $;
cards;
Wealth
Cycle
Educ
Age
Inter
;
Run;

Re: Order rows by user defined criteria

PaigeMiller — Fri, 28 Jan 2022 14:23:09 GMT

I want to order the rows in the way that for each customer there will be the following order or rows

Wealth
Cycle
Educ
Age
Inter

Create a new variable which is one when the value is "Wealth" and 2 when the value is "Cycle" and so on, then sort by this new variable.

Re: Order rows by user defined criteria

PeterClemmensen — Fri, 28 Jan 2022 14:23:03 GMT

Try this

proc sql;
   create table want as
   select category from have
   order by whichc(category, 'Wealth', 
                             'Cycle',
                             'Educ',
                             'Age',
                             'Inter');
quit;

Re: Order rows by user defined criteria

PeterClemmensen — Sun, 30 Jan 2022 11:46:49 GMT

@Ronein if this works for you, please mark it as the solution.

Re: Order rows by user defined criteria

Ronein — Sun, 30 Jan 2022 15:54:36 GMT

It is a good solution but in real world I need to sort by customer ID and then by user defined category order and here it is not working well

Re: Order rows by user defined criteria

Ronein — Sun, 30 Jan 2022 15:55:17 GMT

This is the best solution and works well always . Thank you