topic Select value from list based on ordered preference in SAS Enterprise Guide

Select value from list based on ordered preference

cheema11 — Thu, 29 Sep 2022 02:07:50 GMT

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
;

Re: Select value from list based on ordered preference

PeterClemmensen — Thu, 29 Sep 2022 06:28:49 GMT

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;

Re: Select value from list based on ordered preference

cheema11 — Fri, 30 Sep 2022 00:21:39 GMT

It works perfect. Thanks

Re: Select value from list based on ordered preference

PeterClemmensen — Fri, 30 Sep 2022 05:22:00 GMT

Anytime 🙂