2

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?

Amin Karimi
  • 407
  • 2
  • 16
  • These two operations don't seem to explain how you get the final result. For `(A,7-May-08)` even though one record is outside the interval defined in step 1, you delete the first 3 records yet keep one where the time *is* within the interval. Also, for which of these groups did you only delete the _"first row in this interval"_? Aside from that one where `time` is '09:04:41` you seem to simply be deleting any row where `time` falls between 9:00 and 9:06, regardless of other rows in the group – user2877959 Nov 30 '17 at 15:54
  • I made a mistake for (A,7-May-08) row. So the (A | 7-May-08 | 09:03:20) row add to the final table. Please consider that I just want to delete the first row or the first group with the same time in this interval. – Amin Karimi Nov 30 '17 at 16:56

1 Answers1

1

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;
Petr
  • 376
  • 1
  • 6
  • 1
    What I really like about SAS SQL implementation is allowing you to output columns that are not in the `group by` statement nor are they aggregated columns (time column in our case). When you write a query like this you will notice following message in the log: "NOTE: The query requires remerging summary statistics back with the original data." – Petr Nov 30 '17 at 18:42