topic Re: SAS Date Function in SAS Programming

SAS Date Function

yog_pals — Thu, 23 Jun 2016 06:17:20 GMT

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.

Re: SAS Date Function

RahulG — Thu, 23 Jun 2016 06:28:04 GMT

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.

Re: SAS Date Function

Kurt_Bremser — Thu, 23 Jun 2016 06:55:02 GMT

Please post the log, including the sql step code.

Re: SAS Date Function

yog_pals — Thu, 23 Jun 2016 07:06:00 GMT

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

Re: SAS Date Function

RahulG — Thu, 23 Jun 2016 07:09:05 GMT

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

Then it would help to answer.

Re: SAS Date Function

Kurt_Bremser — Thu, 23 Jun 2016 07:09:55 GMT

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)

Re: SAS Date Function

yog_pals — Thu, 23 Jun 2016 07:11:52 GMT

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

Re: SAS Date Function

yog_pals — Thu, 23 Jun 2016 07:16:04 GMT

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)

Re: SAS Date Function

Kurt_Bremser — Thu, 23 Jun 2016 07:23:22 GMT

@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