topic Deleting consecutive values in SAS Programming

Deleting consecutive values

LudovicTheate — Tue, 29 Nov 2011 20:52:50 GMT

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

Deleting consecutive values

art297 — Tue, 29 Nov 2011 21:33:05 GMT

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;

Re: Deleting consecutive values

MikeZdeb — Wed, 30 Nov 2011 03:32:30 GMT

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

Deleting consecutive values

Ksharp — Wed, 30 Nov 2011 04:38:06 GMT

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