PROC SQL Syntax Error

mariko5797 — Tue, 20 Jul 2021 19:19:57 GMT

Why am I getting a syntax error? It looks like it wants me to have some sort of symbol, but I already have a comma there.

Log:

1058  proc sql noprint;
1059   create table catdem as
1060   /*ETHNICITIES*/
1061      /*NOT HISPANIC OR LATINO - Cohort A*/
1062   select 1 as CAT, 1 as SUBCAT, 'Not Hispanic or Latino' as CHAR,
1063    count(distinct USUBJID) as N_NOB from data where ETHNICN = 1 and BMIGRP = 'Not Obese' and
1063! (ARMCD = 'A' or ARMCD = 'A_AND_B'),
1063    count(distinct USUBJID) as N_NOB from data where ETHNICN = 1 and BMIGRP = 'Not Obese' and
1063! (ARMCD = 'A' or ARMCD = 'A_AND_B'),
                                        -
                                        22
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=,
              <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT,
              HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER,
              OUTER, UNION, ^, ^=, |, ||, ~, ~=.

1063    count(distinct USUBJID) as N_NOB from data where ETHNICN = 1 and BMIGRP = 'Not Obese' and
1063! (ARMCD = 'A' or ARMCD = 'A_AND_B'),
                                        -
                                        76
ERROR 76-322: Syntax error, statement will be ignored.

1064    count(distinct USUBJID) as N_OB from data where ETHNICN = 1 and BMIGRP = 'Obese' and
1064! (ARMCD = 'A' or ARMCD = 'A_AND_B'),
1065    count(distinct USUBJID) as N_SOB from data where ETHNICN = 1 and BMIGRP = 'Severely Obese'
1065!  and (ARMCD = 'A' or ARMCD = 'A_AND_B'),
1066    count(distinct USUBJID) as N from data where ETHNICN = 1 and (ARMCD = 'A' or ARMCD =
1066! 'A_AND_B');
1067  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Code:

proc sql noprint;
 create table catdem as
 /*ETHNICITIES*/
	/*NOT HISPANIC OR LATINO - Cohort A*/
 select 1 as CAT, 1 as SUBCAT, 'Not Hispanic or Latino' as CHAR, 
  count(distinct USUBJID) as N_NOB from data where ETHNICN = 1 and BMIGRP = 'Not Obese' and (ARMCD = 'A' or ARMCD = 'A_AND_B'),
  count(distinct USUBJID) as N_OB from data where ETHNICN = 1 and BMIGRP = 'Obese' and (ARMCD = 'A' or ARMCD = 'A_AND_B'),
  count(distinct USUBJID) as N_SOB from data where ETHNICN = 1 and BMIGRP = 'Severely Obese' and (ARMCD = 'A' or ARMCD = 'A_AND_B'),
  count(distinct USUBJID) as N from data where ETHNICN = 1 and (ARMCD = 'A' or ARMCD = 'A_AND_B');
quit;

Re: PROC SQL Syntax Error

PaigeMiller — Tue, 20 Jul 2021 19:24:21 GMT

In the WHERE clause, you do not use commas. You can place valid Boolean expressions in the WHERE clause.

There are other problems, you can only have one WHERE clause in your PROC SQL main query (and another WHERE in a subquery which you aren't using, so that doesn't apply here)

Re: PROC SQL Syntax Error

Kurt_Bremser — Tue, 20 Jul 2021 20:26:04 GMT

You can have only one FROM clause in a SELECT. I guess you want to use a subselect there.

Re: PROC SQL Syntax Error

ChrisNZ — Tue, 20 Jul 2021 23:30:12 GMT

You can't just invent syntax as you go. This is invalid SQL.

Maybe use something like this to get you started.

proc sql ;                                
  create table TMP as 
  select BMIGRP
       , count(distinct USUBJID) as N 
  where ETHNICN = 1 
    and ARMCD in ('A', 'A_AND_B')
  group by BMIGRP;

  create table CATDEM as
  select 1                                 as CAT
       , 1                                 as SUBCAT
       , 'Not Hispanic or Latino'          as CHAR
       , sum(N*(BMIGRP='Not Obese'))       as N1
       , sum(N*(BMIGRP='Obese'    ))       as N2
       , sum(N*(BMIGRP='Severely  Obese')) as N3
  from TMP ;
quit;

topic PROC SQL Syntax Error in SAS Programming

PROC SQL Syntax Error

Re: PROC SQL Syntax Error

Re: PROC SQL Syntax Error

Re: PROC SQL Syntax Error