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 ;
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
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.