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.
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.
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'
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
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.
The non-matching data types were not detected then because the immediately preceding condition threw an ERROR and the SQL compiler stopped right there.
@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.
That one comes from a different dataset, so this must have strings as "dates".
@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.
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
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)
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.