Deleting consecutive values

[LudovicTheate]

Hello !

I've got a little question about a problem met in my work today. I've got a SAS table which looks like the following one :

Nosinid	ndep	dep
1	1	100
1	2	-100
1	3	100
2	1	500
2	2	-500
2	3	500
2	4	-500
2	5	500
2	6	500
3	1	1000
3	2	-500
3	3	500

I need, for each value of the variable Nosinid, to read the values of dep in the order of ndep, and to delete the pairs (a,-a) with a>0 and the two elements a and -a are consecutive. So the table should become the following one :

Nosinid	ndep	dep
1	3	100
2	5	500
2	6	500
3	1	1000
3	2	-500
3	3	500

Could you explain me how I could perform this operation ? Thanks in advance.

Ludovic

[art297]

There are a number of ways to do what you ask. Here is one method:

data have;

  input Nosinid ndep dep;

  cards;

1          1          100

1          2          -100

1          3          100

2          1          500

2          2          -500

2          3          500

2          4          -500

2          5          500

2          6          500

3          1          1000

3          2          -500

3          3          500

;

data want (drop=skip next:);

  set have;

  by Nosinid;

  retain skip;

  set have ( firstobs = 2 keep = ndep dep

        rename = (ndep = next_ndep

                  dep  = next_dep) )

      have (      obs = 1 drop = _all_ );

  if first.Nosinid then skip=0;

  next_ndep =  ifn(  last.Nosinid, (.), next_ndep );

  next_dep =  ifn(  last.Nosinid, (.), next_dep );

  if skip then skip=0;

  else do;

    if dep gt 0 and sum(dep,next_dep) eq 0 then skip=1;

    if not skip then output want;

  end;

run;

[MikeZdeb]

hi ... this gives the ouput you specified

I added an extra set of observations where the (a, -a) pair crosses values of NOSINID ...

data have;

input Nosinid ndep dep;

cards;

1          1          100

1          2          -100

1          3          100

2          1          500

2          2          -500

2          3          500

2          4          -500

2          5          500

2          6          500

3          1          1000

3          2          -500

3          3          500

4          1          -500

4          2          100

4          3          100

;

run;

data want (drop=n d);

do j=1 to obs;

  set have nobs=obs point=j;

  k = j + 1;

  set have (keep=nosinid dep rename=(nosinid=n dep=d)) point = k;

  if (nosinid eq n) and (dep gt 0) and (dep+d eq 0) then j + 1;

  else output;

end;

stop;

run;

Nosinid    ndep     dep

   1         3       100

   2         5       500

   2         6       500

   3         1      1000

   3         2      -500

   3         3       500

   4         1      -500

   4         2       100

   4         3       100

[Ksharp]

data have;
  input Nosinid ndep dep;
  cards;
1          1          100
1          2          -100
1          3          100
2          1          500
2          2          -500
2          3          500
2          4          -500
2          5          500
2          6          500
3          1          1000
3          2          -500
3          3          500
;
run;
data want(drop=_: flag);
 merge have have(firstobs=2 keep=dep Nosinid rename=(dep=_dep Nosinid=_Nosinid));
 retain flag 0;
 if dep gt 0 and dep+_dep=0 and Nosinid=_Nosinid then do; flag=1;delete; end;
 if flag then do;flag=0;delete; end;
run;

Ksharp