Join two datasets in SAS by a variable, which one is substring from another variable.

[GN0001]

Hello team,

I want to Left join two datasets by a variable, which that variable is part of the other variable from left table.

data LeftTable;
input plan_des: $15 registered;
datalines;
plan blue  12
Florida commerce plan 14
Florida plan ABC
;
Run

data right Table;
input plan_des: $15 registered;
datalines;
plan  15
commerce 14
 ABC
;
Run

The right dataset has only some part of the string from the left dataset. If we want to join them by plan_des, how can we do that?

Regards,

Blue Blue

[andreas_lds]

You may want to use the search function to get some solutions on how to join by partial match. You could use proc sql and index or find to in the where statement.

[GN0001]

Will you use index function in another function?
If yes, what function? Is it substring?
Regards,
Blue blue

[ChrisNZ · Accepted solution]

SQL is your best bet here. One way:

from LEFT left join RIGHT on LEFT.PLAN_DES like cats('%', RIGHT.PLAN_DES, '%')

Another way:

from LEFT left join RIGHT on index(LEFT.PLAN_DES,strip(RIGHT.PLAN_DES))

[GN0001]

Hello,
Thanks for the response:
This is what I have:
from table b Left join a on upcase(a.diagcode) = upcase(b.diagcode)
And upcase(a.subdiagcod)=upcase(b.subdiagcode)
And upcase(a.plan) like cats('%',upcase(plan),'%');
line 266: quit;

log shows errors line 266 in the program. Error 73-322: Expecting an = affected code: !symput('order_id'
shows errors line 266 in the program: Error 180-322: statement is not valid or it is used out of proper order.
Error 202-322: for same line
Error 22-322
Error 201-322

If any tips is all much appreciated.
Regards,
Blue Blue

[SASKiwi]

@GN0001  - Posting incomplete code plus error messages that don't relate to the code isn't helpful.

Please post your complete SAS log including code, notes and errors and use the Insert Code icon (</>) to ensure the formatting isn't mucked up.

[GN0001]

Hello SASKiwi,
I don't know if I responded to you before. Sometime, log belongs to the
code that is for my work, I can't paste it to forum here.
Regards,
blue blue

[SASKiwi]

@GN0001  - Is that because it is confidential? If so just make up a similar example using made-up data or one of SAS's sample tables in the SASHELP library.

[GN0001]

Hello SASKiwi,
Yes, that is confidential. If they realize that I am posting their log, it
has legal consequences.
Sometimes it is not doable to create same thing with SAShelp and my SAs at
work doesn't have SASHELP.

It is very hard to get responses from this forum, that is why we can't see
many professionals in SAS in the market, majority have SAS base
certificate only. I have tried other forums, there are people that want
truly help.

There is one mememer in the community that whenever I ask questions, he
sends a maxim.

Thanks for all you do.
blueblue

[Tom]

Your posted code seems to be for different datasets than your original post.

Use FIND() or FINDW() function because the support the trim and ignore case options.  The difference is whether you want to match when the smaller string is part of a larger word or only when it matches a full word in the larger string.

...
from table b 
  left join a 
  on upcase(a.diagcode) = upcase(b.diagcode)
  and upcase(a.subdiagcod)=upcase(b.subdiagcode)
  and findw(a.plan,b.plan,,'it')
...

[Ksharp]

data LeftTable;
input plan_des & $35. registered;
datalines;
plan blue     12
Florida commerce plan    14
Florida plan ABC     .
;
Run;

data rightTable;
input plan_des: $15. registered;
datalines;
plan    15
commerce  14
BC  .
;
Run;

proc sql;
select a.*,b.registered as b_registered
 from lefttable as a left join righttable as b
  on a.plan_des contains strip(b.plan_des);
quit;

[GN0001]

Hello,

How do you know which characters are stripped off?

Regards,

Blue Blue

[Ksharp]

Function findw() ?

proc sql;
select a.*,b.registered as b_registered
 from lefttable as a left join righttable as b
  on findw( a.plan_des  ,strip(b.plan_des));
quit;