topic Re: How to delete rows by set of two in a group in SAS Enterprise Guide

How to delete rows by set of two in a group

alepage — Thu, 11 Mar 2021 20:19:40 GMT

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
*/

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

jebjur — Thu, 11 Mar 2021 21:46:58 GMT

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;

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

Reeza — Thu, 11 Mar 2021 21:47:07 GMT

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.

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

alepage — Thu, 11 Mar 2021 22:32:09 GMT

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

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

Reeza — Thu, 11 Mar 2021 22:37:05 GMT

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

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

alepage — Thu, 11 Mar 2021 23:04:22 GMT

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

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

mkeintz — Fri, 12 Mar 2021 03:24:46 GMT

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.

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

jebjur — Fri, 12 Mar 2021 19:50:30 GMT

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?