🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Barite | Level 11

## How to delete rows by set of two in a group

DATA TEST;
LENGTH POLICY \$10.;
INFILE DATALINES DLM='|';
INPUT POLICY \$ CODE ;
DATALINES;
AAA|1
AAA|2
AAA|3
AAA|4
AAA|5
AAB|5
AAB|6
AAC|5
;
RUN;

/* If we look at the code, the even code (2,4,6) are related to a cancellation.
so we have a code 1 and code 2 for the same policy, those lines should be deleted.
Same thing for code 3 and code 4, those lines should be deleted.
Again, same thing for code 5 and 6, those lines should be deleted. So at the end, the test table
should contains only the following lines: how do we do that either with sas code or proc sql, quit;
how do we do that?*/

/* AAA|5
AAC|5
*/

1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: How to delete rows by set of two in a group

I understand your task as follows.  Any time there is a code with an even value, there will have already been a code with a value exactly one less for the same policy.  The corresponding records should not be kept.  Keep only records with an odd code value, for which there is no code value exactly 1 greater for the same policy.

If the codes really are integers and the data are sorted by policy/code, then:

``````data want (drop=nxt_:);
set test (keep=policy);
by policy;
merge test
test (firstobs=2 keep=code rename=(code=nxt_code));
if mod(code,2)=1 and (nxt_code^=code+1 or last.policy=1);
run;``````

Using the firstobs=2 you can look ahead one record and compare the code-in-hand against the next code.  If the code in hand is odd and either the next code is one greater, or the current record is the last for the current policy, then it is unmatched and should be output.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
7 REPLIES 7
SAS Employee

## Re: How to delete rows by set of two in a group

This sounds like you want to count the number of observations for each policy number, and if there are an even number of observations, then delete that group. If there are an odd number of observations, then keep the last observation. Does that sound correct? If so, you can use BY-group processing with the MOD function to get the results you want:

For example:

data test2 (drop=cnt);
set test;
by policy;
if first.policy then cnt=0;
cnt+1;
if last.policy then do;
if MOD(cnt,2)=0 then delete;
else output;
end;
run;

Barite | Level 11

## Re: How to delete rows by set of two in a group

I have test your solution with real data and my dataset is empty
SAS Employee

## Re: How to delete rows by set of two in a group

Did you run my code against the test data set you specified in your post? Did it give you the results you wanted? If so, what is different in the test data set vs the real data? Were there any errors in the log when when you ran the code against your real data?

Super User

## Re: How to delete rows by set of two in a group

Sounds like you're trying to make sense of transaction data.

I would create two new variables.
VarOne would map (2, 4, 6) to (1, 3, 5) and the VarTwo would map (2, 4, 6) to (-1, -3, -5), respectively.

Then sum varTwo, grouping by Policy and VarOne.

Keep only records where the total sum is not 0.

The mapping code tables can be automated if it's 1,2 as shown but if it's more complicated than that, you'll have to create it manually.

Barite | Level 11

## Re: How to delete rows by set of two in a group

Hum! you are close to a solution. One thing we must be advise of, usually, when we have a code 2 then we should have the code 1.  When we have a code 4 we should have a code 3.  When we have the code 6 we should have the code 5.  Otherwise, we could have a code 1, or 5 alone.

I was trying the following approach... a select over partition with case statement but it does not seems to work. Do you already have use this approach.:

proc sql;
create table test2 as
(
select policy, code,
row_number() over ( partition by policy
order by case when (code = '1' and code= '2') then 0
when (code = '3' and code= '4') then 1
when (code = '5' and code= '6') then 2
else 3 end ) As rownumber
from test
);

quit;

So there after, you delete everything with rownumber between 0 and two.

Could it works

Super User

## Re: How to delete rows by set of two in a group

No idea, I'm not familiar with PARTITION well enough to read that code without running it. It's not supported in SAS.
PROC Star

## Re: How to delete rows by set of two in a group

I understand your task as follows.  Any time there is a code with an even value, there will have already been a code with a value exactly one less for the same policy.  The corresponding records should not be kept.  Keep only records with an odd code value, for which there is no code value exactly 1 greater for the same policy.

If the codes really are integers and the data are sorted by policy/code, then:

``````data want (drop=nxt_:);
set test (keep=policy);
by policy;
merge test
test (firstobs=2 keep=code rename=(code=nxt_code));
if mod(code,2)=1 and (nxt_code^=code+1 or last.policy=1);
run;``````

Using the firstobs=2 you can look ahead one record and compare the code-in-hand against the next code.  If the code in hand is odd and either the next code is one greater, or the current record is the last for the current policy, then it is unmatched and should be output.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 7 replies
• 974 views
• 0 likes
• 4 in conversation