SAS Date Function

[yog_pals]

Hi Guys

Need your expertise. facing an problem while using Date condition in where clase. i want the data from 1st of May

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_dummy AS
SELECT t1.billingdate
FROM test.dummy t1
WHERE t1.billingdate >= '01May2016'd;
QUIT;

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. 

[RahulG]

It is interesting problem. If you can share the result of proc contents data=test.dummy; run;    

I am interested to know details of the column type.

[Kurt_Bremser]

Please post the log, including the sql step code.

[yog_pals]

1 The SAS System 09:59 Thursday, June 23, 2016

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Query Builder';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 %_eg_conditional_dropds(WORK.QUERY_FOR_dummy);
24
25 PROC SQL;
26 CREATE TABLE WORK.QUERY_FOR_dummy AS
27 SELECT t1.billingdate
28 FROM test.dummy t1
29 WHERE t1.billingdate >= '1May2016'd;
NOTE: Table WORK.QUERY_FOR_dummy created, with 8932083 rows and 1 columns.

30 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 52.48 seconds
cpu time 12.73 seconds


31
32 GOPTIONS NOACCESSIBLE;
33
34
35 %LET _CLIENTTASKLABEL=;
36 %LET _CLIENTPROCESSFLOWNAME=;
37 %LET _CLIENTPROJECTPATH=;
38 %LET _CLIENTPROJECTNAME=;
39
40 ;*';*";*/;quit;run;
41 ODS _ALL_ CLOSE;
42
43
44 QUIT; RUN;
45

[RahulG]

if you can share billing date column data type and its format. 

Then it would help to answer.

[yog_pals]

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
22	archflag	Num	8	6	6	archflag
4	batchserno	Num	8	11	11	batchserno
5	billingdate	Num	8	DATE9.	DATE9.	billingdate

[Kurt_Bremser]

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)

[yog_pals]

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)

[Kurt_Bremser]

---

@yog_pals wrote:
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