DATA Step, Macro, Functions and more

PROC sql: help with troubleshooting

Reply
Regular Contributor
Posts: 192

PROC sql: help with troubleshooting

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;


74


75        CREATE table Results AS


76


77        SELECT *


78


79        FROM ccall


80


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


82


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


                                                  -


                                                  22


                                                  76


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, ^, ^=, |,


              ||, ~, ~=.

Super User
Posts: 17,831

Re: PROC sql: help with troubleshooting

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

Also verify your brackets.

Respected Advisor
Posts: 4,649

Re: PROC sql: help with troubleshooting

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;

PG

PG
Regular Contributor
Posts: 192

Re: PROC sql: help with troubleshooting

Thanks everyone! 

SAS Employee
Posts: 15

Re: PROC sql: help with troubleshooting

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;

Ask a Question
Discussion stats
  • 4 replies
  • 216 views
  • 1 like
  • 4 in conversation