ERROR 79-322: Expecting a SELECT.

[bhca60]

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;

[Reeza]

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. 

[bhca60]

i changed it. 

[Kurt_Bremser]

Is TIN a value or a variable name?

[bhca60]

its an ID number - its a character

[Kurt_Bremser]

---

@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"?

[ballardw]

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;

[bhca60]

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

[pink_poodle]

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;      

[Reeza]

Where is TIN coming from?