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

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

GN0001 — Wed, 30 Jun 2021 04:43:51 GMT

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

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

andreas_lds — Wed, 30 Jun 2021 05:13:18 GMT

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.

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

ChrisNZ — Wed, 30 Jun 2021 05:41:40 GMT

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))

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

Ksharp — Wed, 30 Jun 2021 12:39:49 GMT

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;

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

GN0001 — Wed, 30 Jun 2021 15:45:08 GMT

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

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

GN0001 — Wed, 30 Jun 2021 15:46:20 GMT

Hello,

How do you know which characters are stripped off?

Regards,

Blue Blue

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

GN0001 — Wed, 30 Jun 2021 19:43:10 GMT

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

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

SASKiwi — Wed, 30 Jun 2021 20:02:30 GMT

@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.

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

Ksharp — Thu, 01 Jul 2021 12:09:19 GMT

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;

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

Tom — Thu, 01 Jul 2021 15:17:56 GMT

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')
...

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

GN0001 — Wed, 07 Jul 2021 17:31:36 GMT

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

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

SASKiwi — Wed, 07 Jul 2021 20:08:40 GMT

@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.

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

GN0001 — Wed, 07 Jul 2021 20:24:36 GMT

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