Not applicable
Posts: 1

# Deleting consecutive values

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 :

Nosinidndepdep
11100
12-100
13100
21500
22-500
23500
24-500
25500
26500
311000
32-500
33500

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 :

Nosinidndepdep
13100
25500
26500
311000
32-500

3

3500

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

Ludovic

PROC Star
Posts: 8,169

## Deleting consecutive values

Posted in reply to LudovicTheate

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;

Valued Guide
Posts: 765

## Re: Deleting consecutive values

Posted in reply to LudovicTheate

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

Super User
Posts: 10,787

## Deleting consecutive values

Posted in reply to LudovicTheate
```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

Discussion stats
• 3 replies
• 149 views
• 3 likes
• 4 in conversation