regimen Data:
Name Drg1 Drg2 Drg3
R1 A B C
R2 P Q R
Meds Data:
Id Drg1 Drg2 Drg3
1 A B
2 A B C
3 A C
4 A B C
5 P Q R
6 Q R
The order of drg doesnt matter as long as id has taken all three drgs we need the name from regimen as below. Only id 4 and 5 gets name R1 and R2. How to achieve this?
Id Drg1 Drg2 Drg3 Name
1 A B
2 A B C
3 A C
4 A B C R1
5 P Q R R2
6 Q R
A slightly fleshed out version of @tomrvincent's suggestion:
data sortedmeds; set meds; call sortc (dgr1,drg2,drg3); run; proc sql; create table want as select a.*, b.name from sortedmeds as a left join regiment as b on a.drg1=b.drg1 and a.drg2=b.drg2 and a.drg3=b.drg3 order by a.id; quit;
If the REGIMEN data is not sorted by name for the drg variables follow the pattern in the sorted meds.
I created a different data set just in case some other processing of meds data requires the original order.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.