topic Re: Convert multiple claims into one claim in SAS Programming

Convert multiple claims into one claim

Babloo — Tue, 30 Oct 2018 03:59:18 GMT

I have the data as follows and I need to convert per the desired output. If the claim is having identifier as 1 and 4 then I need to convert it to 0 which means converting duplicate claim into distinct claim.

Input data:

Claim identifier
001 1
001 4

Desired output:

Claim Identifier
001 0

Re: Convert multiple claims into one claim

novinosrin — Tue, 30 Oct 2018 04:14:38 GMT


data have;
input claim $ id;
cards;
001 1 
001 4
;

proc sql;
create table want as
select distinct claim, id not in (1,4) as id
from have
having id=0;
quit;

Re: Convert multiple claims into one claim

Babloo — Tue, 30 Oct 2018 04:25:04 GMT

Thanks. Any other alternate way using data step?

Re: Convert multiple claims into one claim

s_lassen — Tue, 30 Oct 2018 10:01:42 GMT

Here is a datastep solution:

data have;
input claim $ id;
cards;
001 1
001 2 
001 4
002 1
002 3 
002 5
;run;

data want;
  set 
    have(where=(id not in(1,4)))
    have(where=(id=1) in=in1)
    have(where=(id=4) in=in4)
    ;
  by claim;
  if in1 and not last.claim then
    id=0;
  if in4 and lag(id)=0 then
    delete;
run;

You may want to sort by claim and id afterwards, as the zeros, ones and fours are now last in the output.