Below should work.
With Broad as search string use findW() if you search for the exact word, use find() if you search for strings that contain the search string (example: Broadcaster).
Both the find() and findw() function allow for case insensitive search (via i parameter).
If you only need to search through variables if they contain exactly the string you search for then the whichc() function is another option.
1. Create sample data
data snpm;
usubjid+1;
array set1{*} $20 smq01nam smq02nam smq03nam smq04nam smq05nam smq06nam smq07nam smq08nam smq09nam smq10nam (10*'A');
array set2{*} $20 smq01sc smq02sc smq03sc smq04sc smq05sc smq06sc smq07sc smq08sc smq09sc smq10sc (10*'B');
array other{*} aedecod aebodsys trt01an;
output;
usubjid+1;
smq04sc='Broadcaster';
output;
usubjid+1;
smq04sc='Broad';
output; output;
usubjid+1;
call missing(of set1[*]);
output;
run;
2. SQL closest to the code you shared
proc sql;
/* create table ae as*/
select distinct
usubjid
,aedecod
,catx('@', smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,smq09nam, smq10nam))
/*,hashing('md5',catx('@', smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,smq09nam, smq10nam)) as digest length=32*/
,aebodsys
,trt01an
from snpm
where
findw(
catx('@', smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc, smq07sc, smq08sc, smq09sc, smq10sc)
,'Broad','@','i'
)
and
cmiss(smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,smq09nam, smq10nam) ne 10
;
quit;
3. Code variation assuming that you only need the concatenated string for de-duping but don't want to write it to the target table
proc sql;
/* create table ae(drop=n_dups) as*/
select
usubjid
,aedecod
,aebodsys
,trt01an
,count(*) as n_dups
from snpm
where
/* findw(*/
/* catx('@', smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc, smq07sc, smq08sc, smq09sc, smq10sc)*/
/* ,'Broad','@','i'*/
/* )*/
/* find(*/
/* catx('@', smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc, smq07sc, smq08sc, smq09sc, smq10sc)*/
/* ,'Broad','i'*/
/* )*/
whichc('Broad',smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc, smq07sc, smq08sc, smq09sc, smq10sc)>0
and
cmiss(smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,smq09nam, smq10nam) ne 10
group by
usubjid
,aedecod
,aebodsys
,trt01an
,smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,smq09nam, smq10nam
;
quit;
... View more