Exclude a Claim based on multiple conditions

[127]

hello, 

Looking to create a new dataset with the "whole" claim for specific Procedure Code G102 or G103 as being "denied" based on the date of service (DOS). 

Any help with SAS code would be great!  Here's an example with fake data: 

ID	Clm1stDOS	ProcCode	Status	WantData
10	11/1/2015	G102	Paid	Keep
10	11/1/2015	G102	Paid	Keep
10	11/1/2015	G102	Denied	Keep
10	12/18/2015	G102	Paid	Keep
10	12/18/2015	G103	Denied	new dataset
10	12/20/2015	G103	Paid	keep
10	12/20/2015	G102	Denied	new dataset
10	12/20/2015	G102	Denied	new dataset

[PeterClemmensen]

I don't understand the logic here. Can you be a bit more specific?

[127]

Claim with the same DOS can have multiple lines with same or different proc codes and sometimes they get paid and sometimes denied. I want to group the claim as a "whole claim" by their DOS and ProcCode then if the whole claim for the same DOS and same Proc code got denied every time then I want to exclude into separate database. 

[novinosrin · Accepted solution]

HI @127  By any chance, you are after this?

data have;
input ID	Clm1stDOS :mmddyy10.	(ProcCode	Status) ($);*	WantData;
format Clm1stDOS mmddyy10.;
cards;
10	11/1/2015	G102	Paid 	Keep
10	11/1/2015	G102	Paid 	Keep
10	11/1/2015	G102	Denied  	Keep
10	12/18/2015	G102	Paid 	Keep
10	12/18/2015	G103	Denied 	new dataset
10	12/20/2015	G103	Paid	keep
10	12/20/2015	G102	Denied 	new dataset
10	12/20/2015	G102	Denied 	new dataset
;

data keep new;
 do _n_=1 by 1 until(last.ProcCode);
  set have;
  by id Clm1stDOS ProcCode notsorted;
  if status='Denied' then _n=sum(_n,1);
 end;
 if _n_=_n then  _f=1;
 do _n_=1 to _n_;
  set have;
  if _f then output new;
  else output keep;
 end;
 drop _:;
run;

[127]

An elegant solution. thank you!