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?
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,
- Jan.
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.
Choose an informat that corresponds to your FilledMonth strings! We could suggest some if you gave us a sample of FilledMonth values.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.