CODEWITHSUNDEEP
mysqlsqlsas
Amin Karimi
Amin Karimi

Reputation: 407

How to remove a first row or first group of rows in a specific time interval using SAS?

I'm working with SAS and I have a data frame like this:

table1

+------+------------+-----------+
| name |    date    |    time   |
+------+------------+-----------+
| A    |  7-May-08  |  09:01:41 |
| A    |  7-May-08  |  09:01:41 |
| A    |  7-May-08  |  09:03:20 |
| A    |  7-May-08  |  09:04:41 |
| A    |  7-May-08  |  11:32:41 |
| A    |  8-May-08  |  09:06:00 |
| A    |  8-May-08  |  09:06:01 |
| A    |  8-May-08  |  12:32:41 |
| B    |  7-May-08  |  09:00:01 |
| B    |  7-May-08  |  09:00:01 |
| B    |  7-May-08  |  11:33:41 |
| B    |  9-May-08  |  09:05:59 |
| B    |  9-May-08  |  11:35:41 |
| B    |  9-May-08  |  11:36:41 |
| B    |  9-May-08  |  11:37:41 |
| B    |  12-May-08 |  11:27:41 |
| B    |  12-May-08 |  11:27:41 |
+------+------------+-----------+

Now I want to do two main operations:

1- If time value for each name and date variables is between 9:00:01 and 9:05:59 minutes then delete first row in this interval;

2- For the previous step, If the time value of next rows is the same with first row then delete all of them in this interval.

For example table1 at last should be like this:

+------+-----------+----------+
| name |   date    |   time   |
+------+-----------+----------+
| A    | 7-May-08  | 09:03:20 |
| A    | 7-May-08  | 9:04:41  |
| A    | 7-May-08  | 11:32:41 |
| A    | 8-May-08  | 9:06:00  |
| A    | 8-May-08  | 9:06:01  |
| A    | 8-May-08  | 12:32:41 |
| B    | 7-May-08  | 11:33:41 |
| B    | 9-May-08  | 11:35:41 |
| B    | 9-May-08  | 11:36:41 |
| B    | 9-May-08  | 11:37:41 |
| B    | 12-May-08 | 11:27:41 |
| B    | 12-May-08 | 11:27:41 |
+------+-----------+----------+

How can I do that?

Upvotes: 2

Views: 318

Answers (1)

Petr
Petr

Reputation: 376

I assume that the data is ordered by time for each group of name + date for the purpose of "If the time value of next rows is the same with first row".

Then, the query is pretty simple:

data have;
input @1 name $1 @3 date date11. @13 time time.;
format date date11.;
format time time.;
datalines;
A 7-May-08  09:01:41
A 7-May-08  09:01:41
A 7-May-08  09:03:20
A 7-May-08  09:04:41
A 7-May-08  11:32:41
A 8-May-08  09:06:00
A 8-May-08  09:06:01
A 8-May-08  12:32:41
B 7-May-08  09:00:01
B 7-May-08  09:00:01
B 7-May-08  11:33:41
B 9-May-08  09:05:59
B 9-May-08  11:35:41
B 9-May-08  11:36:41
B 9-May-08  11:37:41
B 12-May-08 11:27:41
B 12-May-08 11:27:41
;
run;

proc sql;
create table want as select *
from have
group by name, date
having min(time) not between '09:00:01't and '09:05:59't
    or time ne min(time)
;quit;

Upvotes: 1

Related Questions