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
;
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;
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;
Anytime 🙂
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.