Proc sql-creating new table not working

[billi_billi]

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;

[SASKiwi · Accepted solution]

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'.

[billi_billi]

@SASKiwi  Thank you, this worked.

[Patrick]

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;