topic Re: PROC sql: help with troubleshooting in SAS Programming

PROC sql: help with troubleshooting

jcis7 — Mon, 06 May 2013 21:46:42 GMT

Hello! The following code has an error. Can you help troubleshoot?

PROC SQL;

CREATE table Results AS

SELECT *

FROM ccall

WHERE (status='Reported' AND enrollment NE 0)

AND status='Not Reported' (WHERE code IN (SELECT number FROM DS));

QUIT;

I want to retain all that've reported with nonzero enrollment and all that've not reported that have codes located in a separate table named DS.... But I'm getting an error message below. Any help you can give is much appreciated! Thanks!

__________________

LOG:

73 PROC SQL;

75 CREATE table Results AS

77 SELECT *

79 FROM ccall

81 WHERE (status='Reported' AND enrollment NE 0)

83 AND status='Not Reported' (WHERE code IN (SELECT

83 ! number FROM dss));

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <,

<=, <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING,

INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |,

||, ~, ~=.

Re: PROC sql: help with troubleshooting

Reeza — Mon, 06 May 2013 21:58:00 GMT

You can't have two where in the query, that should probably be an AND instead.

Also verify your brackets.

Re: PROC sql: help with troubleshooting

PGStats — Mon, 06 May 2013 22:02:22 GMT

Try

PROC SQL;

CREATE table Results AS

SELECT *

FROM ccall

WHERE

status = 'Reported' AND

enrollment NE 0 AND

status = 'Not Reported' AND

code IN (SELECT number FROM DS);

QUIT;

Re: PROC sql: help with troubleshooting

jcis7 — Tue, 07 May 2013 22:20:49 GMT

Thanks everyone!

Re: PROC sql: help with troubleshooting

Vladislaff — Wed, 19 Jun 2013 13:09:55 GMT

PROC SQL;

CREATE table Results AS

SELECT *

FROM ccall

WHERE

(status = 'Reported' AND

enrollment NE 0) OR

(status = 'Not Reported' AND

code IN (SELECT number FROM DS));

QUIT;