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 !!
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;
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;
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
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.
Please post a datastep with some test data which matches exactly (ie. same formats) the data you are using.
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,
K
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.