ERROR: Expecting a SELECT ??

[bhca60]

23         proc sql;
24         create table claims
25         as select
26         cpt,
27         cptdescription,
28         cost,
29         eventDate,
30         icd,
31         icdDescription,
32         providerNPI,
33         providerName,
34         providerSpecialty,
35         memberNo
36         from x.eventLineDetails
37         where providerSpecialty = 'Dermatology'
38         and eventDate between "2020-12-01" and "2021-12-31"
39         and claimatRisk=1;
NOTE: Table WORK.CLAIMS created, with 138947 rows and 10 columns.

40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           5.21 seconds
      cpu time            1.73 seconds
      

41         
42         
43         /*from pharmacyEvents table*/
44         
45         proc sql;
46         create table pharmacy
47         as select
48         memberNo,
2                                                          The SAS System                            09:40 Friday, February 25, 2022

49         cost,
50         prescriberNPI,
51         prescriberName,
52         prescriberSpecialty,
53         paidDate
54         from x.pharmacyEvents
55         where prescriberSpecialty in 'Dermatology'
                                        _____________
                                        79
                                        76
ERROR 79-322: Expecting a SELECT.

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

56         and paidDate between "2020-12-01" and "2021-12-31"
57         and claimatRisk=1;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
58         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

Not sure why it requires a SELECT statement there.  The first proc sql works and the second is very similar to that one so not sure why it's erroring out.

[Kurt_Bremser]

When you compare to a single value, use = instead of IN. The IN operator needs a list enclosed in parentheses.

[bhca60]

yeah i tried equal sign too and it gave an error still

[Reeza]

Well, it says the types mismatch. That means PrescriberSpeciality is likely a numeric column not a character column. What is the type and format on that column? It may be numeric with a format applied to make it look like Dermatology?

[PaigeMiller]

where prescriberSpecialty in ('Dermatology')

or in this case, since there is only one item in the IN clause you could also use

where prescriberSpecialty eq 'Dermatology'

[Reeza]

And that error is generated because "IN" in SQL is typically followed by either a list of values or a subquery (SELECT) that generates a list of values.

SAS doesn't know which one you're trying to do, so the error doesn't quite align with what you're trying to accomplish in this case.

[bhca60]

I tried the equal sign, and it gave me this (as if I'm still using the IN operator):

23         proc sql;
24         create table pharmacy
25         as select
26         memberNo,
27         cost,
28         prescriberNPI,
29         prescriberName,
30         prescriberSpecialty,
31         paidDate
32         from x.pharmacyEvents
33         where prescriberSpecialty = 'Dermatology'
34         and paidDate between "2020-12-01" and "2021-12-31"
35         and claimatRisk=1;
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
36         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.19 seconds
      cpu time            0.03 seconds
      

[FreelanceReinh · Accepted solution]

I think it's more likely that paidDate is a numeric variable containing SAS date values (as it should) so that the date condition should read

paidDate between '01DEC2020'd and '31DEC2021'd

Edit: Looking at your first PROC SQL step where the similar condition

eventDate between "2020-12-01" and "2021-12-31"

did not cause a type mismatch I'm not so sure that paidDate really contains SAS date values, only that eventDate does not contain SAS date values. So you should check what kind of numeric values paidDate contains. The reason why I think they are numeric is that I would expect type mismatches regarding prescriberSpecialty or claimatRisk to result in the error message

ERROR: Expression using equals (=) has components that are of different data types.

which is different from what you've shown.

[Kurt_Bremser]

The non-matching data types were not detected then because the immediately preceding condition threw an ERROR and the SQL compiler stopped right there.

[FreelanceReinh]

---

@Kurt_Bremser wrote:

The non-matching data types were not detected then because the immediately preceding condition threw an ERROR and the SQL compiler stopped right there.

---

I see what you mean, but I referred to the first of the two PROC SQL steps (which had a clean log) in the initial post when I wrote that the condition using eventDate did not cause a type mismatch.

[Kurt_Bremser]

That one comes from a different dataset, so this must have strings as "dates".

[bhca60]

Thank you, it was the dates!

[FreelanceReinh]

---

@bhca60 wrote:
Thank you, it was the dates!

---

You're welcome. However, later readers of this thread might be confused about the accepted solution as it is unrelated to the error message in the subject line and in your initial post. (The date issue surfaced only when you corrected the first error.)

I think it would be more appropriate if you marked one of the first three replies, which answered the original question, as the accepted solution. It's very easy to change this: Select the most helpful post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

[AMSAS]

Why are your padDate values strings/character?

and paidDate between "2020-12-01" and "2021-12-31"

That doesn't look right, I would expect them to be SAS date values 

[Kurt_Bremser]

Most likely because you use incorrect date literals. They must be of the form

'01jan2022'd

(DATE9. format, enclosed in quotes with  an immediately trailing d)