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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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