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!
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;
Pay attention to the MODIFIER options that the functions has. There is a case-insensitive mode for the funciton. SAS Help Center: FINDW Function
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.
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.