topic Re: Date filter in Proc Sql in SAS Procedures

Date filter in Proc Sql

PreenaKaur — Mon, 13 Jul 2015 05:14:19 GMT

hello

I am struggling with filtering records from table on date criteria.

Format in which Dates are stored in my table are :

01JUN2015:08:45:29

I am able to extract records when i am using below query :

PROC SQL;

CREATE TABLE WORK.TEST AS

SELECT * FROM DETAIL.ABC

where

open_time between '01Jun15'd

and

'01Jul15'd;

quit;

But when i change the dates to generic one ( first date of previous month and first date of current month ) then its not working , I am getting all the records instead of filtered one

PROC SQL;

CREATE TABLE WORK.TEST AS

SELECT * FROM DETAIL.ABC

where

open_time

between INTNX('MONTH',TODAY(),-1,'B')

and

INTNX('MONTH',TODAY(),0,'B');

quit;

Do anyone have an idea where I am wrong !!

Re: Date filter in Proc Sql

RW9 — Mon, 13 Jul 2015 09:02:31 GMT

Sorry, from information you have given I don't see how the first one is working correctly either. You have a variable which is datetime = "01JUN2015:08:45:29". This is stored internally as a number, the number of seconds since a certain timepoint. If you just do a simple datastep, and remove the format from the variables:

data temp;

a="01JUN2015:08:45:29"dt;

b=intnx('month',today(),-1,'b');

run;

You will quite clearly see that the numeric representation of a date (number of days since a certain timepoint) is in no way relational to the representation of a datetime (number of seconds since a certain timepoint).

So you need to convert the datetime to a date, to do this use the datepart() function (note it is also advisable to use consistent formatting, and indetation for readability):

proc sql;

create table WORK.TEST as

select *

from DETAIL.ABC

where datepart(OPEN_TIME) between intnx('MONTH',today(),-1,'B') and intnx('MONTH',today(),0,'B');

quit;

Re: Date filter in Proc Sql

KeyuriPatel — Mon, 13 Jul 2015 09:07:43 GMT

Hi,

i tried the above with some sample data and with the format with which you are working and i have got the outputs with the same code as you are using. i tired with the below mentioned codes:

PROC SQL;

CREATE TABLE WANT AS

SELECT * FROM SAMPLE_DATA

where ORDERDATE between '01Jun15'd and '01Jul15'd;

quit;

PROC SQL;

CREATE TABLE WANT_DATA AS

SELECT * FROM SAMPLE_DATA

where

ORDERDATE between INTNX('MONTH',TODAY(),-1,'B')AND INTNX('MONTH',TODAY(),0,'B');

quit;

DATA DATA_WANT;

SET SAMPLE_DATA;

WHERE ORDERDATE BETWEEN INTNX('MONTH',TODAY(),-1,'b') AND INTNX('MONTH',TODAY(),0,'b');

RUN;

the below mentioned can be useful: (just for reference)

data _null_;

today= today();

nextweek =intnx('week',today(),1);

lastweek =intnx('week',today(),-1);

firstdaynextmonth =intnx('month',today(),1,'b');

lastdaylastmonth =intnx('month',today(),-1,'e');

samedaylastmonth =intnx('month',today(),-1,'s');

middlelastmonth =intnx('month',today(),-1,'m');

lastdaynextquater =intnx('qtr',today(),1,'e');

put 'Today is ' today :date9.;

put 'Next week ' nextweek :date9.;

put 'LastWeek is ' lastweek :date9.;

put 'The First Day of Next Month is ' firstdaynextmonth :date9.;

put 'The Last Day of Last Month is ' lastdaylastmonth :date9.;

put 'The Same Day of Last Month is ' samedaylastmonth :date9.;

put 'The Middle of Last Month is ' middlelastmonth :date9.;

put 'The Last Day of Next Quater is ' lastdaynextquater :date9.;

run;

hope it helps...

regards,

Re: Date filter in Proc Sql

PreenaKaur — Tue, 14 Jul 2015 04:08:45 GMT

I tried with datepart as well but no luck

Its not returning any rows with or without using datepart ..

log shows : 0 records and 94 columns

I have checked the records in table, there are rows for same timeperiod

Re: Date filter in Proc Sql

Reeza — Tue, 14 Jul 2015 04:41:54 GMT

Please post a proc contents output for your input data set - specifically the date variable as well as the code/log with date part function that you say is not working.

Re: Date filter in Proc Sql

RW9 — Tue, 14 Jul 2015 08:12:33 GMT

Please post a datastep with some test data which matches exactly (ie. same formats) the data you are using.

Re: Date filter in Proc Sql

PreenaKaur — Fri, 17 Jul 2015 06:25:52 GMT

Hey All,

Following did worked:

%LET DATE=%SYSFUNC(DATETIME());

%LET MONTH_END_DT = %SYSFUNC(INTNX(DTMONTH, &DATE.,-1,E));

%LET MONTH_START_DT = %SYSFUNC(INTNX(DTMONTH, &DATE.,-1,B));

%PUT MONTH_END_DT = %SYSFUNC(PUTN(&MONTH_END_DT, DATETIME19.));

%PUT MONTH_START_DT = %SYSFUNC(PUTN(&MONTH_START_DT, DATETIME19.));

PROC SQL;

CREATE TABLE WORK.TEST AS

SELECT

ID ,

OPEN_TIME,

&MONTH_START_DT AS Y FORMAT DATETIME19.,

&MONTH_END_DT AS X FORMAT DATETIME19.

FROM DETAIL.ABC

WHERE &MONTH_START_DT. LE OPEN_TIME LE &MONTH_END_DT.;

QUIT;