Hello Everyone.
I have a table with a read time column and what I want to do is based on the read time column, if there are some early records with datetime value between current record’s readtime – 31*60 (readtime is a datetime. Format column) and readtime – 29*60, then keep the current record. Loop through from the first record to the last record in this table. (between 31 mins and 29 mins).
Not sure how I can do this? seems like I need to a loop but just not sure how to check if there is a record between the time limit...
Any suggestions and advice?
Thank you!
Tao
Here's an SQL version. Of course, instead of (time-29) you need to use (time-29*60), or the intnx() function.
data have;
do id=1 to 1000;
time+ranuni(123);
output;
end;
run;
proc sql;
create table want as
select l.id, l.time, r.id as id2, r.time as time2
from have as l, have as r
where r.time between l.time-29 and l.time-31;
quit;
Hi Tao,
Could you post an example of data what you have, and what results you want?
What you describe is a self-join? For example, if there are 3 records within the 29-31 minute interval, you want 3 rows generated for the current row?
If I understand you correctly, you can do thin with an SQL self-join, or (if you have lots of records) after sorting the data set by time, you need one carefully written datastep to accomplish this.
Hi Gergely,
Hi Thank you fro your reply! Yes, as you described if there are 3 rows in the range, then yes, I want to keep all of them. The codes you mentioned below, I have think about this method by creating a new table with two extra columns as t1=time-29 and t2=time-31. However, I didn't continue because I don't think you can join two tables without common variable. Your code just remind me that I can actually do cartesian join
Thank you for the help again and I will try the code and see how it goes.
Tao
Here's an SQL version. Of course, instead of (time-29) you need to use (time-29*60), or the intnx() function.
data have;
do id=1 to 1000;
time+ranuni(123);
output;
end;
run;
proc sql;
create table want as
select l.id, l.time, r.id as id2, r.time as time2
from have as l, have as r
where r.time between l.time-29 and l.time-31;
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.