how to delete observation between two different rows using sas [SAS]

Prev: join two tables from different databases that are on the same server
Next: Tracking read/write/modify access of SAS files?

From: Xiaofei Zhang on 30 Jul 2010 18:25

data example:
obs type date id time number index
1 B11 20030303 1301 900 1
2 B12 20030303 1301 900 2
3 B12 20030303 1301 902 1
4 B12 20030303 1301 904 1
5 B11 20030303 1301 905 1
6 B12 20030303 1301 905 2
7 B12 20030303 1301 910 3
8 B12 20030303 1301 910 5
9 B12 20030303 1301 911 1
10 B12 20030303 1301 913 1
11 B11 20030303 1301 913 3 1
12 B12 20030303 1301 913 4
13 B12 20030303 1301 914 1
14 B11 20030303 1301 915 1
15 B12 20030303 1301 915 2
16 B12 20030303 1301 917 1
17 B11 20030303 1301 931 1
18 B12 20030303 1301 931 2
19 B12 20030303 1301 932 1
20 B11 20030303 1301 933 5
21 B12 20030303 1301 933 6
22 B12 20030303 1301 934 1
23 B12 20030303 1301 934 3
24 B11 20030303 1301 934 5 2
25 B12 20030303 1301 934 6
26 B12 20030303 1301 936 1
27 B12 20030303 1301 942 1
28 B11 20030303 1301 950 1
29 B12 20030303 1301 950 2
30 B12 20030303 1301 950 4
31 B11 20030303 1301 952 1
32 B12 20030303 1301 952 2
33 B12 20030303 1301 955 1
34 B11 20030303 1301 956 1
35 B12 20030303 1301 956 2
36 B11 20030303 1301 957 1
37 B12 20030303 1301 957 2
38 B12 20030303 1301 957 4
39 B11 20030303 1301 958 1 3
40 B12 20030303 1301 958 2
41 B11 20030303 1301 959 1

when the type = B11 and index ne ., I want to delete the observation
which lie between the previous 'B11' and the after 'B11', and the
observation's type is 'B12'.
In other words, when the index=1, I want to delete the observation
from 6 to 13; when the index=2, I want to delete the observation from
21 to 27; when the index=3, I want to delete the observation from 37
to 40.

My data have millions of observation, different id, different date,
different time, different number, and the index has more 20,000. How
to solve this question?

Thank you so much!
-William

From: Ya on 30 Jul 2010 18:53

On Jul 30, 3:25 pm, Xiaofei Zhang <shawfee....(a)gmail.com> wrote:
> data example:
> obs type date id time number index
> 1 B11 20030303 1301 900 1
> 2 B12 20030303 1301 900 2
> 3 B12 20030303 1301 902 1
> 4 B12 20030303 1301 904 1
> 5 B11 20030303 1301 905 1
> 6 B12 20030303 1301 905 2
> 7 B12 20030303 1301 910 3
> 8 B12 20030303 1301 910 5
> 9 B12 20030303 1301 911 1
> 10 B12 20030303 1301 913 1
> 11 B11 20030303 1301 913 3 1
> 12 B12 20030303 1301 913 4
> 13 B12 20030303 1301 914 1
> 14 B11 20030303 1301 915 1
> 15 B12 20030303 1301 915 2
> 16 B12 20030303 1301 917 1
> 17 B11 20030303 1301 931 1
> 18 B12 20030303 1301 931 2
> 19 B12 20030303 1301 932 1
> 20 B11 20030303 1301 933 5
> 21 B12 20030303 1301 933 6
> 22 B12 20030303 1301 934 1
> 23 B12 20030303 1301 934 3
> 24 B11 20030303 1301 934 5 2
> 25 B12 20030303 1301 934 6
> 26 B12 20030303 1301 936 1
> 27 B12 20030303 1301 942 1
> 28 B11 20030303 1301 950 1
> 29 B12 20030303 1301 950 2
> 30 B12 20030303 1301 950 4
> 31 B11 20030303 1301 952 1
> 32 B12 20030303 1301 952 2
> 33 B12 20030303 1301 955 1
> 34 B11 20030303 1301 956 1
> 35 B12 20030303 1301 956 2
> 36 B11 20030303 1301 957 1
> 37 B12 20030303 1301 957 2
> 38 B12 20030303 1301 957 4
> 39 B11 20030303 1301 958 1 3
> 40 B12 20030303 1301 958 2
> 41 B11 20030303 1301 959 1
>
> when the type = B11 and index ne ., I want to delete the observation
> which lie between the previous 'B11' and the after 'B11', and the
> observation's type is 'B12'.
> In other words, when the index=1, I want to delete the observation
> from 6 to 13; when the index=2, I want to delete the observation from
> 21 to 27; when the index=3, I want to delete the observation from 37
> to 40.
>
> My data have millions of observation, different id, different date,
> different time, different number, and the index has more 20,000. How
> to solve this question?
>
> Thank you so much!
> -William

data ex;
input obs type $ date $ id time number index;
cards;
1 B11 20030303 1301 900 1 .
2 B12 20030303 1301 900 2 .
3 B12 20030303 1301 902 1 .
....

;

data ex1;
set ex;
if type='B11' and index=. then grp+1;
run;

proc sql;
create table ex2 as
select *, max(index) > 0 and (type='B12' or type='B11' and index>0) as
flag
from ex1
group by grp
order by obs
;

proc print;
var obs flag;
run;

obs flag

1 0
2 0
3 0
4 0
5 0
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 1
22 1
23 1
24 1
25 1
26 1
27 1
28 0
29 0
30 0
31 0
32 0
33 0
34 0
35 0
36 0
37 1
38 1
39 1
40 1
41 0

You can easily delete records for flag=1.

HTH

Ya

From: Ya on 30 Jul 2010 18:55

On Jul 30, 3:25 pm, Xiaofei Zhang <shawfee....(a)gmail.com> wrote:
> data example:
> obs type date id time number index
> 1 B11 20030303 1301 900 1
> 2 B12 20030303 1301 900 2
> 3 B12 20030303 1301 902 1
> 4 B12 20030303 1301 904 1
> 5 B11 20030303 1301 905 1
> 6 B12 20030303 1301 905 2
> 7 B12 20030303 1301 910 3
> 8 B12 20030303 1301 910 5
> 9 B12 20030303 1301 911 1
> 10 B12 20030303 1301 913 1
> 11 B11 20030303 1301 913 3 1
> 12 B12 20030303 1301 913 4
> 13 B12 20030303 1301 914 1
> 14 B11 20030303 1301 915 1
> 15 B12 20030303 1301 915 2
> 16 B12 20030303 1301 917 1
> 17 B11 20030303 1301 931 1
> 18 B12 20030303 1301 931 2
> 19 B12 20030303 1301 932 1
> 20 B11 20030303 1301 933 5
> 21 B12 20030303 1301 933 6
> 22 B12 20030303 1301 934 1
> 23 B12 20030303 1301 934 3
> 24 B11 20030303 1301 934 5 2
> 25 B12 20030303 1301 934 6
> 26 B12 20030303 1301 936 1
> 27 B12 20030303 1301 942 1
> 28 B11 20030303 1301 950 1
> 29 B12 20030303 1301 950 2
> 30 B12 20030303 1301 950 4
> 31 B11 20030303 1301 952 1
> 32 B12 20030303 1301 952 2
> 33 B12 20030303 1301 955 1
> 34 B11 20030303 1301 956 1
> 35 B12 20030303 1301 956 2
> 36 B11 20030303 1301 957 1
> 37 B12 20030303 1301 957 2
> 38 B12 20030303 1301 957 4
> 39 B11 20030303 1301 958 1 3
> 40 B12 20030303 1301 958 2
> 41 B11 20030303 1301 959 1
>
> when the type = B11 and index ne ., I want to delete the observation
> which lie between the previous 'B11' and the after 'B11', and the
> observation's type is 'B12'.
> In other words, when the index=1, I want to delete the observation
> from 6 to 13; when the index=2, I want to delete the observation from
> 21 to 27; when the index=3, I want to delete the observation from 37
> to 40.
>
> My data have millions of observation, different id, different date,
> different time, different number, and the index has more 20,000. How
> to solve this question?
>
> Thank you so much!
> -William

data ex;
input obs type $ date $ id time number index;
cards;
1 B11 20030303 1301 900 1 .
2 B12 20030303 1301 900 2 .
3 B12 20030303 1301 902 1 .
4 B12 20030303 1301 904 1 .
....
;

data ex1;
set ex;
if type='B11' and index=. then grp+1;
run;

proc sql;
create table ex2 as
select *, max(index) > 0 and (type='B12' or type='B11' and index>0) as
flag
from ex1
group by grp
order by obs
;

proc print noobs;
var obs flag;
run;

obs flag

1 0
2 0
3 0
4 0
5 0
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 0
15 0
16 0
17 0
18 0
19 0
20 0
21 1
22 1
23 1
24 1
25 1
26 1
27 1
28 0
29 0
30 0
31 0
32 0
33 0
34 0
35 0
36 0
37 1
38 1
39 1
40 1
41 0

You can easily delete those flag=1 records.

HTH

Ya

From: Xiaofei Zhang on 30 Jul 2010 20:18

On Jul 31, 6:55 am, Ya <huang8...(a)gmail.com> wrote:
> On Jul 30, 3:25 pm, Xiaofei Zhang <shawfee....(a)gmail.com> wrote:
>
>
>
> > data example:
> > obs type date id time number index
> > 1 B11 20030303 1301 900 1
> > 2 B12 20030303 1301 900 2
> > 3 B12 20030303 1301 902 1
> > 4 B12 20030303 1301 904 1
> > 5 B11 20030303 1301 905 1
> > 6 B12 20030303 1301 905 2
> > 7 B12 20030303 1301 910 3
> > 8 B12 20030303 1301 910 5
> > 9 B12 20030303 1301 911 1
> > 10 B12 20030303 1301 913 1
> > 11 B11 20030303 1301 913 3 1
> > 12 B12 20030303 1301 913 4
> > 13 B12 20030303 1301 914 1
> > 14 B11 20030303 1301 915 1
> > 15 B12 20030303 1301 915 2
> > 16 B12 20030303 1301 917 1
> > 17 B11 20030303 1301 931 1
> > 18 B12 20030303 1301 931 2
> > 19 B12 20030303 1301 932 1
> > 20 B11 20030303 1301 933 5
> > 21 B12 20030303 1301 933 6
> > 22 B12 20030303 1301 934 1
> > 23 B12 20030303 1301 934 3
> > 24 B11 20030303 1301 934 5 2
> > 25 B12 20030303 1301 934 6
> > 26 B12 20030303 1301 936 1
> > 27 B12 20030303 1301 942 1
> > 28 B11 20030303 1301 950 1
> > 29 B12 20030303 1301 950 2
> > 30 B12 20030303 1301 950 4
> > 31 B11 20030303 1301 952 1
> > 32 B12 20030303 1301 952 2
> > 33 B12 20030303 1301 955 1
> > 34 B11 20030303 1301 956 1
> > 35 B12 20030303 1301 956 2
> > 36 B11 20030303 1301 957 1
> > 37 B12 20030303 1301 957 2
> > 38 B12 20030303 1301 957 4
> > 39 B11 20030303 1301 958 1 3
> > 40 B12 20030303 1301 958 2
> > 41 B11 20030303 1301 959 1
>
> > when the type = B11 and index ne ., I want to delete the observation
> > which lie between the previous 'B11' and the after 'B11', and the
> > observation's type is 'B12'.
> > In other words, when the index=1, I want to delete the observation
> > from 6 to 13; when the index=2, I want to delete the observation from
> > 21 to 27; when the index=3, I want to delete the observation from 37
> > to 40.
>
> > My data have millions of observation, different id, different date,
> > different time, different number, and the index has more 20,000. How
> > to solve this question?
>
> > Thank you so much!
> > -William
>
> data ex;
> input obs type $ date $ id time number index;
> cards;
> 1 B11 20030303 1301 900 1 .
> 2 B12 20030303 1301 900 2 .
> 3 B12 20030303 1301 902 1 .
> 4 B12 20030303 1301 904 1 .
> ...
> ;
>
> data ex1;
> set ex;
> if type='B11' and index=. then grp+1;
> run;
>
> proc sql;
> create table ex2 as
> select *, max(index) > 0 and (type='B12' or type='B11' and index>0) as
> flag
> from ex1
> group by grp
> order by obs
> ;
>
> proc print noobs;
> var obs flag;
> run;
>
> obs flag
>
> 1 0
> 2 0
> 3 0
> 4 0
> 5 0
> 6 1
> 7 1
> 8 1
> 9 1
> 10 1
> 11 1
> 12 1
> 13 1
> 14 0
> 15 0
> 16 0
> 17 0
> 18 0
> 19 0
> 20 0
> 21 1
> 22 1
> 23 1
> 24 1
> 25 1
> 26 1
> 27 1
> 28 0
> 29 0
> 30 0
> 31 0
> 32 0
> 33 0
> 34 0
> 35 0
> 36 0
> 37 1
> 38 1
> 39 1
> 40 1
> 41 0
>
> You can easily delete those flag=1 records.
>
> HTH
>
> Ya

It works, thank you so much!

|
Pages: 1
Prev: join two tables from different databases that are on the same server
Next: Tracking read/write/modify access of SAS files?