How can I create a table based on a readtime Column?

[yangtaotai]

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

[gergely_batho]

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.

[yangtaotai]

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

[gergely_batho · Accepted solution]

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;