topic Re: PROC SQL Date Range in SAS Procedures

PROC SQL Date Range

JediApprentice — Wed, 22 Jun 2016 17:28:47 GMT

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?

Re: PROC SQL Date Range

jklaverstijn — Wed, 22 Jun 2016 17:39:44 GMT

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.

Re: PROC SQL Date Range

JediApprentice — Wed, 22 Jun 2016 18:22:10 GMT

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.

Re: PROC SQL Date Range

PGStats — Wed, 22 Jun 2016 20:25:38 GMT

Choose an informat that corresponds to your FilledMonth strings! We could suggest some if you gave us a sample of FilledMonth values.