Help using Base SAS procedures

Date filter in Proc Sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Date filter in Proc Sql

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 !!


Accepted Solutions
Solution
‎07-17-2015 02:25 AM
Occasional Contributor
Posts: 11

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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;

Occasional Contributor
Posts: 11

Re: Date filter in Proc Sql

I tried with datepart as well but no luck Smiley Sad

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


Super User
Posts: 19,870

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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.

Super User
Super User
Posts: 7,997

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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

Learner
Posts: 1

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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... Smiley Happy

regards,

K

Solution
‎07-17-2015 02:25 AM
Occasional Contributor
Posts: 11

Re: Date filter in Proc Sql

Posted in reply to PreenaKaur

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 5045 views
  • 0 likes
  • 4 in conversation