Exact match

[SASPhile]

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

[tomrvincent]

left join to regimen by the sorted 3 drgs should do it.

[ballardw]

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.