Proc sql-creating new table not working

billi_billi — Fri, 26 Apr 2024 22:42:17 GMT

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;

Re: Proc sql-creating new table not working

SASKiwi — Fri, 26 Apr 2024 23:39:37 GMT

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

Re: Proc sql-creating new table not working

Patrick — Sat, 27 Apr 2024 01:45:59 GMT

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;

Re: Proc sql-creating new table not working

billi_billi — Mon, 29 Apr 2024 13:27:46 GMT

@SASKiwi Thank you, this worked.

topic Re: Proc sql-creating new table not working in SAS Programming

Proc sql-creating new table not working

Re: Proc sql-creating new table not working

Re: Proc sql-creating new table not working

Re: Proc sql-creating new table not working