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;
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)
You can have only one FROM clause in a SELECT. I guess you want to use a subselect there.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.