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 🙂
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!
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.