06-22-2016 01:28 PM
I'm trying to specify a range of dates in my where clause, but the dates (FilledMonth and Date literal) are of different formats. FilledMonth is in the format YYMMw. (2015M01). Here is my code:
PROC SQL; CREATE TABLE EGTASK.NC_SAVINGS AS SELECT t1.FilledMonth, t1.Client, t1.Business_Line, t1.Segment, t1.MACSavingsGrouping AS Channel, t1.Specialty_Flag, t1.BRAND_GENERIC, t1.ApprovedPriceType, t1.SUM_OF_AWP, t1.SUM_OF_APPROVEDINGREDIENTCOST, t1.SUM_OF_ADFA, t1.SUM_OF_RX FROM EGTASK.All_Combined_Table t1 WHERE (FilledMonth BETWEEN '01jan2015'd AND '31dec2015'd) AND CLIENT = 'BCBS NC'; QUIT;
The error message I'm receiving is: "Expression using IN has components that are of different data types." There is no IN, I understand this to mean that the dates are of different formats. How do I get the date literals into same format as FilledMonth?
06-22-2016 01:39 PM
First thing to check is the datatype of FilledMonth. Easy to do as this seems to be Enterprise Guide.
But I must assume it is character and has the character representation of the date. Use the INPUT() function to convert to a proper date value (integer, #days since 01JAN1960). 2015M01 will translate to January first, 2015.
WHERE (INPUT(FilledMonth, MONYY8.) BETWEEN '01jan2015'd AND '31dec2015'd) AND CLIENT = 'BCBS NC';
Hope this helps,
06-22-2016 02:22 PM
I tried this solution and it didn't work. I get the error message: ERROR: INPUT function reported 'ERROR: Invalid date value' while processing WHERE clause. The variable FilledMonth is character and it has a format of $7.