/*****************************************************************************************************************
 
SAS file name: custom_sort.sas
File location: 
__________________________________________________________________________________________________________________
 
Purpose: To demonstrate how to apply custom sorts in SAS with PROC FORMAT and PROC SQL
Author: Peter Clemmensen
Creation Date: 17/01/2020
 
This program supports the blog post "Custom Sort in SAS" on SASnrd.com

*****************************************************************************************************************/

/* Example Data */
data numbers;
input x @@;
datalines;
1 3 2 2 3 4 2 3 1 4
;

data country;
input country $ 1-25;
datalines;
Denmark                  
United States of America 
Germany                  
France                   
Canada                   
China                    
Russia                   
Mexico                   
;

/* Use PROC FORMAT */
proc format;
   invalue $ c
   'United States of America' = 1
   'China'                    = 2
   other                      = 3
   ;
run;

data temp / view=temp;
   set country;
   s = input (country, $c.);
run;

proc sort data=temp out=want (drop=s);
   by s;
run;

/* Use the SQL Procedure and conditional logic */
proc sql;
   create table want as
   select country from country
   order by case (country)
      when 'United States of America' then 1
      when 'China'                    then 2
      else                                 3
    end
    ;
quit;

/* Use the Which or Whichc Function in Order By Clause */
proc sql;
   create table want as
   select country from country
   order by whichc(country, 'China', 
                            'United States of America')
   descending;
quit;

/* For numeric variables */
proc sql;
   create table want as
   select x from have
   order by whichn(x, 2, 1, 3, 4);
quit;