DATA Step, Macro, Functions and more

SAS Date Function

Reply
Occasional Contributor
Posts: 7

SAS Date Function

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. 

Regular Contributor
Posts: 242

Re: SAS Date Function

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.

Super User
Posts: 6,972

Re: SAS Date Function

Please post the log, including the sql step code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: SAS Date Function

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
Regular Contributor
Posts: 242

Re: SAS Date Function

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

Then it would help to answer.

Occasional Contributor
Posts: 7

Re: SAS Date Function

Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
22archflagNum866archflag
4batchsernoNum81111batchserno
5billingdateNum8DATE9.DATE9.billingdate
Super User
Posts: 6,972

Re: SAS Date Function

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: SAS Date Function

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)
Super User
Posts: 6,972

Re: SAS Date Function


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 8 replies
  • 351 views
  • 1 like
  • 3 in conversation