BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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

2 REPLIES 2
tomrvincent
Rhodochrosite | Level 12
left join to regimen by the sorted 3 drgs should do it.
ballardw
Super User

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 758 views
  • 1 like
  • 3 in conversation