I have a sas data set which has date field which is in the format "04JAN2012" and using format, I am converting it to "2012-01-04" in a separate data step. The problem arises when I am using a simple where statement in proc SQL which is using a 'where' condition like---- select * from temp where temp.active_date > '2012-01-01'
The error message at this point is "Expression using equals (>) has components that are of different data types."
Please help !
The best way would be to convert your string ('2012-01-01') into a SAS date before doing the filter, eg via macro:
%let date_filter='2012-01-01';
%let mydate=%sysfunc(mdy(
%substr(&date_filter,7,2)
,%substr(&date_filter,10,2)
,%substr(&date_filter,2,4)));
proc sql;
select * from temp where temp.active_date > &mydate;
If you're using FORMAT and not PUT (which converts to character internally), you aren't actually changing anything about the date beyond how it is displayed (and used in some grouping functions). Internally, active_date still is a numeric SAS date.
As such, you simply need to use the correct format of date constant. While you are using PROC SQL, you still are in SAS, which formats date constants differently than SQL Server or Oracle.
'01JAN2012'd is the correct format of date constant for that, so your code should be:
select * from temp where temp.active_date > '01JAN2012'd;
If you are generating this comparison value in some fashion (not writing it into code), you can generate it properly by PUTting (or FORMATting) the value being generated to DATE9. format.
