BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PreenaKaur
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PreenaKaur
Calcite | Level 5

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

PreenaKaur
Calcite | Level 5

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


Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

KeyuriPatel
Calcite | Level 5

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

PreenaKaur
Calcite | Level 5

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 22661 views
  • 0 likes
  • 4 in conversation