BookmarkSubscribeRSS Feed
JediApprentice
Pyrite | Level 9

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?

 

 

3 REPLIES 3
jklaverstijn
Rhodochrosite | Level 12

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.

JediApprentice
Pyrite | Level 9

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.

PGStats
Opal | Level 21

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

PG

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 11946 views
  • 0 likes
  • 3 in conversation