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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.