Delete specific rows in data

[monali]

Following is the example of data I have. I want to delete the rows where ClaimStatus = reversed and also delete the associated 'filled' claim. E.g. For ID = 1, I want to delete rows 2 and 3. For ID= 2, I want to delete rows 1 and 2. For ID=3, I want to delete 1 and 2. Please advise on how do I delete these rows from my data. Thanks.

ID	TransactionDate	Payer	ClaimAmountPaid	Copay	ContractCost	RxId	RefillNumber	NDC	fillDate	Quantity	DaysSupply	ClaimStatus
1	abc	1	25	0	11.69	1234	0	xzy	25Feb2016	1	1	Filled
1	abc	1	0	25	11.69	1234	0	xyz	25Feb2016	1	1	Filled
1	abc	-1	0	-25	-11.69	1234	0	xyz	25Feb2016	-1	-1	Reversed
2	efg	-1	-769.5	-50	-775.36	5678	2	lmn	11Nov2017	-30	-30	Reversed
2	efg	1	769.5	50	775.36	5678	2	lmn	11Nov2017	30	30	Filled
2	efg	1	360.35	50	387.68	5678	2	lmn	15/11/2017	15	30	Filled
3	cde	-1	0	-14.59	-0.648	8910	5	xyz	15/11/2017	-30		Reversed
3	cde	1	0	14.59	0.648	8910	5	xyz	15/11/2017	30		Filled
3	cde	1	0	4	0.648	8910	5	xyz	15/11/2017	30		Filled

[PeterClemmensen]

Is RxOutcome a variable? in that case, I don't see it in your example data?

[monali]

sorry, the variable is ClaimStatus. I edited the original post. 

[PeterClemmensen]

Ok. First part of the problem is solved below. However, what exactly constitutes an 'associated filled claim'? I do see the claims you refer to in the data, but what is the exact logic to put in a program?

data have;
input ID TransactionDate $ Payer ClaimAmountPaid Copay ContractCost RxId RefillNumber NDC $ fillDate:anydtdte12. Quantity DaysSupply $ ClaimStatus $;
infile datalines missover;
format fillDate date9.;
datalines;
1 abc 1 25 0 11.69 1234 0 xzy 25Feb2016 1 1 Filled
1 abc 1 0 25 11.69 1234 0 xyz 25Feb2016 1 1 Filled
1 abc -1 0 -25 -11.69 1234 0 xyz 25Feb2016 -1 -1 Reversed
2 efg -1 -769.5 -50 -775.36 5678 2 lmn 11Nov2017 -30 -30 Reversed
2 efg 1 769.5 50 775.36 5678 2 lmn 11Nov2017 30 30 Filled
2 efg 1 360.35 50 387.68 5678 2 lmn 15/11/2017 15 30 Filled
3 cde -1 0 -14.59 -0.648 8910 5 xyz 15/11/2017 -30 Reversed
3 cde 1 0 14.59 0.648 8910 5 xyz 15/11/2017 30 Filled
3 cde 1 0 4 0.648 8910 5 xyz 15/11/2017 30 Filled
;

data want;
   set have;
   where ClaimStatus ne 'Reversed';
run;

[monali]

The associated 'filled' claim has positive contract cost. See rows 2 and 3 for ID=1. The contractCost is 11.69 for row where ClaimStatus = filled and contractCost is (-11.69) where claimStatus = reversed. And, for both these rows (that I want to exclude) the columns RxId, RefillNumber, NDC, and fillDate need to have the same value. 

[novinosrin]

data have;
infile cards truncover;
input (ID	TransactionDate	Payer	ClaimAmountPaid	Copay	ContractCost	RxId	RefillNumber	NDC	fillDate	Quantity	DaysSupply	ClaimStatus) (:$30.);
cards;
1	abc	1	25	0	11.69	1234	0	xzy	25-Feb-16	1	1	Filled
1	abc	1	0	25	11.69	1234	0	xyz	25-Feb-16	1	1	Filled
1	abc	-1	0	-25	-11.69	1234	0	xyz	25-Feb-16	-1	-1	Reversed
2	efg	-1	-769.5	-50	-775.36	5678	2	lmn	11-Nov-17	-30	-30	Reversed
2	efg	1	769.5	50	775.36	5678	2	lmn	11-Nov-17	30	30	Filled
2	efg	1	360.35	50	387.68	5678	2	lmn	15/11/2017	15	30	Filled
3	cde	-1	0	-14.59	-0.648	8910	5	xyz	15/11/2017	-30	.	Reversed
3	cde	1	0	14.59	0.648	8910	5	xyz	15/11/2017	30	.	Filled
3	cde	1	0	4	0.648	8910	5	xyz	15/11/2017	30	.	Filled
;

data want;
merge have have(firstobs=2 keep= claimstatus id rename= (id =_id claimstatus=_claimstatus));
_k=lag(quantity);
if (id=_id and claimstatus ne _claimstatus) or sum(_k,quantity)=0 then _f=1;
if not _f;
drop _:;
run;

[novinosrin · Accepted solution]

@monali  This is much safer 

data have;
infile cards truncover;
input (ID	TransactionDate	Payer	ClaimAmountPaid	Copay	ContractCost	RxId	RefillNumber	NDC	fillDate	Quantity	DaysSupply	ClaimStatus) (:$30.);
cards;
1	abc	1	25	0	11.69	1234	0	xzy	25-Feb-16	1	1	Filled
1	abc	1	0	25	11.69	1234	0	xyz	25-Feb-16	1	1	Filled
1	abc	-1	0	-25	-11.69	1234	0	xyz	25-Feb-16	-1	-1	Reversed
2	efg	-1	-769.5	-50	-775.36	5678	2	lmn	11-Nov-17	-30	-30	Reversed
2	efg	1	769.5	50	775.36	5678	2	lmn	11-Nov-17	30	30	Filled
2	efg	1	360.35	50	387.68	5678	2	lmn	15/11/2017	15	30	Filled
3	cde	-1	0	-14.59	-0.648	8910	5	xyz	15/11/2017	-30	.	Reversed
3	cde	1	0	14.59	0.648	8910	5	xyz	15/11/2017	30	.	Filled
3	cde	1	0	4	0.648	8910	5	xyz	15/11/2017	30	.	Filled
;
data want;
_n=0;
do _n_=1 by 1 until(last.id);
set have;
by id;
array t(999);
k=sum(Quantity,lag(Quantity));
if k=0 then do; _n+1;t(_n)=_n_-1;_n+1;t(_n)=_n_;end;
end;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_ not in t then output;
end;
drop _: t:;
run;

[monali]

Thanks. This works!