BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
billi_billi
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

View solution in original post

3 REPLIES 3
SASKiwi
PROC Star

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
Calcite | Level 5

@SASKiwi  Thank you, this worked.

Patrick
Opal | Level 21

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;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 776 views
  • 0 likes
  • 3 in conversation