Help using Base SAS procedures

PROC SQL Date Range

Reply
Frequent Contributor
Posts: 123

PROC SQL Date Range

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?

 

 

Super Contributor
Posts: 438

Re: PROC SQL Date Range

Posted in reply to JediApprentice

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.

Frequent Contributor
Posts: 123

Re: PROC SQL Date Range

Posted in reply to jklaverstijn

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.

Respected Advisor
Posts: 4,919

Re: PROC SQL Date Range

Posted in reply to JediApprentice

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

PG
Ask a Question
Discussion stats
  • 3 replies
  • 773 views
  • 0 likes
  • 3 in conversation