SAS Proc SQL error question 1

[DataNick]

Hello All!
I'm new to SAS and am encountering the following error

52 proc sql;
53 SELECT cd.*
54 FROM HCI_sql.DM_Claim_Diagnosis cd
55 INNER JOIN HCI_sql.DM_Claim c
56 ON cd.strClaim = c.strClaim
57 AND c.datAdmit BETWEEN '20051101' AND '20061231'
58 ORDER BY cd.strClaim, cd.strSource, cd.strFormType ASC;
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.
59 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.00 seconds


when running this code:

proc sql;
SELECT cd.*
FROM HCI_sql.DM_Claim_Diagnosis cd
INNER JOIN HCI_sql.DM_Claim c
ON cd.strClaim = c.strClaim
AND c.datAdmit BETWEEN '20051101' AND '20061231'
ORDER BY cd.strClaim, cd.strSource, cd.strFormType ASC;
quit;

In our SQL Server datawarehouse the code run fine as such:

SELECT cd.*
FROM DM_Claim_Diagnosis cd
INNER JOIN DM_Claim c
ON cd.strClaim = c.strClaim
AND c.datAdmit BETWEEN '20051101' AND '20061231'
ORDER BY cd.strClaim, cd.strSource, cd.strFormType ASC;

Any ideas on my syntax problem in SAS.
I suspect it's my usage of aliases not conforming to Proc SQL.
Thanks!

Nick

 

[klaz2002]

You have to determine the data type of the datadmit variable. SQL is very forgiving when using text to compare numbers. For the BETWEEN statement your data types must match. I am willing to bet that the date var is in fact a numeric SAS date variable. If that is the case then try the following code: (If its a SAS datetime var than you will have to use the datepart() function)

proc sql;
 SELECT cd.*
 FROM HCI_sql.DM_Claim_Diagnosis cd
 INNER JOIN HCI_sql.DM_Claim c
 ON cd.strClaim = c.strClaim
 AND  c.datAdmit BETWEEN '01nov2005'd AND '31Dec2006'd
 ORDER BY cd.strClaim, cd.strSource, cd.strFormType ASC; 
quit;

Notice I have converted the date conditions to SAS numeric date values.
I hope that this helps you.
Klaz
Happy holidays

 

[DataNick]

Klaz,
You were dead on right!
datAdmit is a DATETIME in SQL Server, but I guess when my SAS library brings the data in from SQL Server, this field is recognized as a numeric SAS date variable.
Because of this, I didn't realize that within a Proc Sql step, SAS still needs to have dates formatted.

Learning little nuances like this!

Merry Christmas! Happy Hannukah! Happy New Year!

Nick