Join on substring from a variable

[David_Billa]

I've the two datasets (Have 1, Have 2) which has data as follows. I want to to create a target table based on matching values from 'Have2'. When I say matching values, it doesn't mean all values but only few value from Have 1 with Have 2 to get desired Output. For example,

Have 1 dataset looks like,

SUB_S	        ID
2718_AP1	78.76
2718_AP2-A	23.76
2718_AP2-B	23.76
2718_AP3-A	97.09
2718_AP3-C	97.09

Have 2 dataset  looks like,

SUB_S	        LP_ACS_S
2718_AP1	2718_ACS_AP1
2718_AP2-A	2718_ACS_AP2
2718_AP2-C	2718_ACS_AP2
2718_AP3-A	2718_ACS_AP3
2718_AP3-B	2718_ACS_AP3

Desired output is, given below. It's just a left join but if you look at the observations 2 and 4, we do have matching value in 'Have2'. In my desired output, I need the value and the logic is comparing '2718' and 'AP2' from SUB_S variable from 'Have1' with 'Have2' to get the corresponding LP_ACS_S value.

SUB_S	        ID	LP_ACS_S
2718_AP1	78.76	2718_ACS_AP1
2718_AP2-A	23.76	2718_ACS_AP2
2718_AP2-B	23.76	2718_ACS_AP2
2718_AP3-A	97.09	2718_ACS_AP3
2718_AP3-C	97.09	2718_ACS_AP3

[novinosrin]

data have1;
input SUB_S	 :$15.      ID;
cards;
2718_AP1	78.76
2718_AP2-A	23.76
2718_AP2-B	23.76
2718_AP3-A	97.09
2718_AP3-C	97.09
;

data have2;
input (SUB_S	        LP_ACS_S) (:$15.);
cards;
2718_AP1	2718_ACS_AP1
2718_AP2-A	2718_ACS_AP2
2718_AP2-C	2718_ACS_AP2
2718_AP3-A	2718_ACS_AP3
2718_AP3-B	2718_ACS_AP3
;

proc sql;
create table want as
select distinct a.*,LP_ACS_S
from have1 a left join have2 b
on substr(a.SUB_S,1,8)=substr(b.SUB_S,1,8);
quit;

[David_Billa]

Thanks. What if the values from 'Sub_S' in Have2 dataset has values like below and no change in Have1 dataset.

2718_A_AP2
2718_C_AP2
2718_B_AP3
Now what function will you use in 'on' condition?

[novinosrin]

Hi @David_Billa  -

on substr(a.SUB_S,1,8)=catx('_',scan(b.SUB_S,1,'_'),scan(b.SUB_S,3,'_'));