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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.