## How to sort a data set OBS by different sorting order in same step

Super Contributor
Posts: 324

# How to sort a data set OBS by different sorting order in same step

Dear,

I need to sort the data by different sorting order based on  Total value.

if Total is not equal to 0 then sort order should be "descending total id".

if total is equal =0 then sort order should be "descending total descending placebo id"

The  OBS (id=d and id=e) has total values equal=0. As per alphabetical order the id=d should come first. But I need id=e should come before id=d as its placebo value is greater than id=d.

Output needed;

b 4 8 12 3
i 6 6 12 1
c 7 4 11 0
j 7 3 10 0
a 2 4 6 1
f 1 0 1 2
g 1 0 1 3
e 0 0 0 3
d 0 0 0 2

```data one;
input id\$ _10mg _20mg Total placebo;
datalines;
a 2 4 6 1
c 7 4 11 0
b 4 8 12 3
d 0 0 0 2
e 0 0 0 3
f 1 0 1 2
g 1 0 1 3i 6 6 12 1j 7 3 10 0
;```
Super User
Posts: 8,214

## Re: How to sort a data set OBS by different sorting order in same step

[ Edited ]

Here is one way:

```data need;
set one;
sortorder=total;
if Total eq 0 then sortorder+(-.1*sortorder+.01*placebo);
run;

proc sort data=need out=want;
by descending sortorder id;
run;
```

Art, CEO, AnalystFinder.com

Posts: 4,779

## Re: How to sort a data set OBS by different sorting order in same step

[ Edited ]

@knveraraju91

More out of curiosity I've tried the following SQL which to my surprise actually worked.

``````proc sql feedback;
create table want1 as
select *
from one as o
order by ifn(Total eq 0,Total+(-.1*Total+.01*placebo),Total) DESC
;
quit;

proc sql feedback;
create table want as
select *
from one as o
order by
case
when total ne 0 then Total+(-.1*Total+.01*placebo)
else total
end
DESC
;
quit;``````

I'm not proposing to use this code as it's too much of "smart arse". Just wanted to share as it amused me.

Discussion stats
• 2 replies
• 145 views
• 2 likes
• 3 in conversation