Hello,
I am trying to use proc sql to create a new table (code below) but getting an error ( log screenshot attached). Could anyone please help me with this issue? Thank you.
proc sql;
create table ae as
select distinct usubjid, aedecod,
catx('@', smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,
smq09nam, smq10nam) as aesmq,
aebodsys, trt01an
from snpm
where 'Broad' in (smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc, smq07sc, smq08sc,
smq09sc, smq10sc)
and not missing(catx('@', smq01nam, smq02nam, smq03nam, smq04nam, smq05nam, smq06nam, smq07nam, smq08nam,
smq09nam, smq10nam));
quit;
Your use of the IN operator is incorrect - you can only compare constants using IN, not columns. You could do something like this to avoid a lot of typing:
where cats(smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc,smq07sc, smq08sc,smq09sc, smq10sc) contains 'Broad'
That assumes one or more of the columns contains 'Broad'.
Your use of the IN operator is incorrect - you can only compare constants using IN, not columns. You could do something like this to avoid a lot of typing:
where cats(smq01sc, smq02sc, smq03sc, smq04sc, smq05sc, smq06sc,smq07sc, smq08sc,smq09sc, smq10sc) contains 'Broad'
That assumes one or more of the columns contains 'Broad'.
@SASKiwi Thank you, this worked.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.