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.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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