Select value from list based on ordered preference

[cheema11]

I am trying to get a row based on ordered preference on list column. Preference is DCE_CMD, DCE_DA, DCE_New.

Input Table - Master:

Keyword	List
BINA	DCE_CMD
BINA	DCE_DA
BINA	DCE_New
ABC	DCE_DA
ABC	DCE_New
XYZ	DCE_New
IJK	DCE_CMD

Output:

Keyword	List
BINA	DCE_CMD
ABC	DCE_DA
XYZ	DCE_New
IJK	DCE_CMD

data master;
  infile datalines truncover;
  input keyword $ list $;
  datalines;
BINA DCE_CMD
BINA DCE_DA
BINA DCE_New
ABC DCE_DA
ABC DCE_New
XYZ DCE_New
IJK DCE_CMD
;

[PeterClemmensen · Accepted solution]

One way

data have;
input Keyword $ List $;
datalines;
BINA DCE_CMD 
BINA DCE_DA  
BINA DCE_New 
ABC  DCE_DA  
ABC  DCE_New 
XYZ  DCE_New 
IJK  DCE_CMD 
;

proc sql;
   create table want as
   select * 
   from have
   group by Keyword
   having whichc(List, 'DCE_CMD', 'DCE_DA', 'DCE_New') 
    = min(whichc(List, 'DCE_CMD', 'DCE_DA', 'DCE_New'));
quit;

[cheema11]

It works perfect. Thanks

[PeterClemmensen]

Anytime 🙂