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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.