DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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.

Please help. Thank you

 

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 3
i 6 6 12 1
j 7 3 10 0 ;
PROC Star
Posts: 7,480

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

[ Edited ]
Posted in reply to knveraraju91

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

 

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to knveraraju91

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

Ask a Question
Discussion stats
  • 2 replies
  • 119 views
  • 2 likes
  • 3 in conversation