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

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

knveraraju91 — Fri, 23 Jun 2017 21:41:48 GMT

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
;

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

art297 — Fri, 23 Jun 2017 22:14:59 GMT

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

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

Patrick — Sat, 24 Jun 2017 05:03:56 GMT

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