BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
cheema11
Calcite | Level 5

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:

KeywordList
BINADCE_CMD
BINADCE_DA
BINADCE_New
ABCDCE_DA
ABCDCE_New
XYZDCE_New
IJKDCE_CMD

 

Output:

KeywordList
BINADCE_CMD
ABCDCE_DA
XYZDCE_New
IJKDCE_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
;
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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
Calcite | Level 5
It works perfect. Thanks

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 972 views
  • 0 likes
  • 2 in conversation