BookmarkSubscribeRSS Feed
yog_pals
Calcite | Level 5

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. 

8 REPLIES 8
RahulG
Barite | Level 11

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.

yog_pals
Calcite | Level 5
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
Barite | Level 11

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

Then it would help to answer.

yog_pals
Calcite | Level 5
Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
22archflagNum866archflag
4batchsernoNum81111batchserno
5billingdateNum8DATE9.DATE9.billingdate
Kurt_Bremser
Super User

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
Calcite | Level 5
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
Super User

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1197 views
  • 1 like
  • 3 in conversation