How to find strings that contain values from other variables?

[eduong58]

Hi,

I'm a newbie to SAS I have 2 datasets, and I want to accomplish as below. 

data one;
input Description $80.;
cards;
Orange Test - Orange, All Ages - Capital
Test Apple ABC - <20, Male - Capital
Test Pear ABC - <20, Male - Capital
Test Apple - 21+ Years, Female - Capital
Pear Eligible Test - Pear Eligible, All Ages - Region
Pear Test - Pear Eligible, All Ages - Region
Other SBH - Other, All Ages - South Central
;

data two;
input key1 $ key2 $;
cards;
CAPITAL APPLE
CAPITAL PEAR
CAPITAL OTHER
CAPITAL ORANGE
REGION SSI
REGION PEAR
SOUTH OTHER
;

Output should look like this. List out all descriptions that contain  both key1 and key2 values : 

key1	key2	Descr
CAPITAL	APPLE	Test Apple ABC - <20, Male - Capital
CAPITAL	APPLE	Test Apple - 21+ Years, Female - Capital
CAPITAL	PEAR	Test Pear ABC - <20, Male - Capital
CAPITAL	OTHER	not found
CAPITAL	ORANGE	Orange Test - Orange, All Ages - Capital
REGION	SSI	not found
REGION	PEAR	Pear Eligible Test - Pear Eligible, All Ages - Region
REGION	PEAR	Pear Test - Pear Eligible, All Ages - Region
SOUTH	OTHER	Other SBH - Other, All Ages - South Central

I have tried findw  "if findw(one.Description,two.key1)>0 and findw(one.Description,two.key2)>0

then Descr=one.Description else Descr= 'not found' ", but it does not work.

I really appreciate your help. Thank you in advance!

[PhilC]

Pay attention to the MODIFIER options that the functions has.  There is a case-insensitive mode for the funciton.  SAS Help Center: FINDW Function

[ballardw]

Is this supposed to always be a case insensitive comparison? You show "APPLE" matching "Apple" as an example of insensitive.

Are compound words supposed to match? "apple" and "applesauce" for example.

Since FINDW is likely one of the proper tools then you need go through the steps below:

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[FreelanceReinh · Accepted solution]

Hi @eduong58 and welcome to the SAS Support Communities!

I would use PROC SQL:

proc sql;
create table want as
select t.*, coalesce(description,'not found') as Descr
from two t left join one
on findw(upcase(description),trim(key1))
 & findw(upcase(description),trim(key2))
order by key1, key2;
quit;

This doesn't maintain the sort order of the input datasets, though. If you need this, create DATA step views with (temporary) sort keys from datasets ONE and TWO, use the views in the FROM clause and the sort keys in the ORDER BY clause:

data _one / view=_one;
set one;
_seqno1=_n_;
run;

data _two / view=_two;
set two;
_seqno2=_n_;
run;

proc sql;
create table want as
select key1, key2, coalesce(description,'not found') as Descr
from _two left join _one
on findw(upcase(description),trim(key1))
 & findw(upcase(description),trim(key2))
order by _seqno2, _seqno1;
drop view _one, _two;
quit;

[sbxkoenk]

Hello,

I can imagine less greedy solutions exist but this should work.

data _NULL_;
 if 0 then set one nobs=count;
 call symput('numobs',strip(put(count,8.)));
 STOP;
run;
%PUT&=numobs;

data three;
 set two;
 do pointer=1 to &numobs.;
  set one point=pointer;
  if     find(Description,key1,'it')^=0
     AND find(Description,key2,'it')^=0
   then output;
 end;
run;

Cheers,

Koen

[sbxkoenk]

Sorry, you still have to build upon the above program to include the lines with Description='not found'.

I hadn't read the 'assignment' thoroughly enough.

Koen

[eduong58]

Thank you so much for all of your help! I found out the issue is case insensitive. I changed that to all uppercase like @FreelanceReinhard and it works perfect!