topic Re: ERROR 79-322: Expecting a SELECT. in SAS Enterprise Guide

ERROR 79-322: Expecting a SELECT.

bhca60 — Wed, 13 Jul 2022 19:07:32 GMT

Not sure what this means. I'm trying to get list of IDs but I get an error.


23         proc sql;
24         create table data
25         as select
26         a.*,
27         b.Type,
28         b.ID
29         from A inner join B
30         on a.npi=b.id
31         where Type in TIN and
                                   ___
                                   79
                                   76
ERROR 79-322: Expecting a SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

32         ID in ('111111111' '222222222' '333333333' '66666666' '77777777');
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
34         quit;

Re: ERROR 79-322: Expecting a SELECT.

Reeza — Wed, 13 Jul 2022 18:53:35 GMT

SHould this be your query?

         proc sql;
         create table data
         as select
         a.*,
         b.Type,
         b.ID
         from A inner join B
         on a.npi=b.provnpi
         where Type = TIN
         and b.ID in ('111111111' '222222222' '333333333' '66666666' '77777777');

Note that you may want to provide alias on your variables in the WHERE clause, ie b.ID or a.ID as appropriate.

Re: ERROR 79-322: Expecting a SELECT.

Kurt_Bremser — Wed, 13 Jul 2022 18:59:33 GMT

Is TIN a value or a variable name?

Re: ERROR 79-322: Expecting a SELECT.

bhca60 — Wed, 13 Jul 2022 19:08:08 GMT

its an ID number - its a character

Re: ERROR 79-322: Expecting a SELECT.

bhca60 — Wed, 13 Jul 2022 19:12:34 GMT

i changed it.

Re: ERROR 79-322: Expecting a SELECT.

ballardw — Wed, 13 Jul 2022 19:29:34 GMT

The IN operator requires a list of values appearing inside ( ), even for a single value in general.

Where you use "Type in TIN" and there is no ( then Proc SQL is expecting a sub-query starting with SELECT to provide the list.

Run these two steps and see if the second comes closer to what you expected given the small size of SASHELP.CLASS.

Proc sql;
   select *
   from sashelp.class
   where sex in F
   ;
quit;

Proc sql;
   select *
   from sashelp.class
   where sex in ('F')
   ;
quit;

Re: ERROR 79-322: Expecting a SELECT.

bhca60 — Wed, 13 Jul 2022 19:30:35 GMT

I tried = sign and it gave an error to that as well which is why I did the IN operator.

Re: ERROR 79-322: Expecting a SELECT.

pink_poodle — Wed, 13 Jul 2022 19:37:42 GMT

proc sql;
        CREATE TABLE data AS
         SELECT a.*, b.Type, b.ID
         FROM A inner join B
         ON a.npi=b.provnpi
         WHERE Type in (select TIN from a) /* not sure a or b */
         and b.ID in ('111111111' '222222222' '333333333' '66666666' '77777777')
;   
quit;

Re: ERROR 79-322: Expecting a SELECT.

Reeza — Wed, 13 Jul 2022 19:42:19 GMT

Where is TIN coming from?

Re: ERROR 79-322: Expecting a SELECT.

Kurt_Bremser — Wed, 13 Jul 2022 21:31:53 GMT

@bhca60 wrote:

its an ID number - its a character

You have not answered my question. Is it a variable named TIN or the value "TIN"?