06-23-2016 02:17 AM
Need your expertise. facing an problem while using Date condition in where clase. i want the data from 1st of May
CREATE TABLE WORK.QUERY_FOR_dummy AS
FROM test.dummy t1
WHERE t1.billingdate >= '01May2016'd;
The output was from 5th of Jan.
the value is stored as date in the billingdate field. if i run a sql query in query builder the output is from 1st of may. only when i use SAS EG am facing this problem.
Please note - if i give WHERE t1.billingdate >= '05JAN2016'd; in this way am getting results from 1st of May.
can you guys please suggest what is the correct format to use.
06-23-2016 02:55 AM
Please post the log, including the sql step code.
06-23-2016 03:06 AM
06-23-2016 03:09 AM
What do you get when you run
data query_for_dummy1; set test.dummy (keep=billingdate); where billingdate >= '01May2016'd; run;
(if you get dates before May 2016, once again post the log)
06-23-2016 03:23 AM
Still the same result. output coming from 5th Jan 2016. do we have to change the SAS date format or system date format. now it is setup as US Date (system Date)
No, you don't have to change the settings. SAS date constants and date values are not affected by the system's settings.
next piece of test code:
data query_for_dummy; set test.dummy (keep=billingdate); where billingdate = '01May2016'd; run; proc sort data=query_for_dummy; by billingdate; run; data _null_; set query_for_dummy (obs=1); put billingdate=; run;
and post the log of this