topic Re: How can I create a table based on a readtime Column? in SAS Procedures

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

yangtaotai — Fri, 06 Mar 2015 04:29:28 GMT

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

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

gergely_batho — Fri, 06 Mar 2015 09:39:27 GMT

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.

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

gergely_batho — Fri, 06 Mar 2015 09:52:35 GMT

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;

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

yangtaotai — Fri, 06 Mar 2015 13:57:11 GMT

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