Hello
I want to merge data set aaa and data set bbb .
I want to check if any value of column Team in data set aaa exists in data set bbb.
The way of checking is:
step1- delete blanks (before,between and after ) in column Team in data seta aaa
step2- delete blanks (before,between and after ) in column Team in data seta bbb
Step3- check if calculated Team column in data set aaa exists in data set b
What is the way to do it please?
Data aaa;
input team $ 1-20;
cards;
London
Tel aviv
Berlin
Warsaw
Tokyo
;
Run;
Data bbb;
input team $ 1-20;
cards;
Inc London city
Telaviv
Western Berlin
;
Run;
data aaa_new;
set aaa;
team_new=COMPRESS(team);
drop team;
rename team_new=team;
run;
data bbb_new;
set bbb;
team_new=COMPRESS(team);
drop team;
rename team_new=team;
run;
Should Berlin match Western Berlin ?
Yes, because name Berlin included in name West Berlin
Please use the search-function, partial matching has been discussed many times, you should be able to adapt one of the solutions.
Hello,
You can try as follows :
Data aaa;
input team $ 1-20;
cards;
London
Tel aviv
Berlin
Warsaw
Tokyo
;
Run;
Data bbb;
input team $ 1-20;
cards;
Inc London city
Telaviv
Western Berlin
;
Run;
proc sql noprint;
CREATE TABLE want AS
SELECT a.team, b.team AS team_b
FROM aaa a
LEFT JOIN bbb b
ON compress(b.team) CONTAINS compress(a.team)
;
quit;
Or, to have more matches:
proc sql ;
CREATE TABLE want AS
SELECT unique a.team, b.team AS team_b
FROM aaa a
LEFT JOIN bbb b
ON find(compress(b.team), compress(a.team), 'i')
| find(compress(a.team), compress(b.team), 'i') ;
quit;
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.