In the below dataset, I want to convert the identifier like 0s to 1s, 1s to 4s,missing as missing. After the conversion if the claim is having the identifier as 1's and 4's then I've to convert it to 0's. e.g. If the claim has two rows, one with identifier 1 and the other with identifier 4, then I've to convert it to one row and it should have identifier 0.
Input dataset:
claim | Identifier |
123 | . |
456 | 1 |
789 | 0 |
999 | 4 |
999 | 1 |
output dataset:
claim | Identifier |
123 | . |
456 | 4 |
789 | 1 |
999 | 0 |
@Babloo wrote:
Desired output is,
Claim Identifier
222 0
333 4
444 0
456 1
555 1
789 0
999 0
See this:
data have;
input claim identifier;
cards;
123 .
222 1
222 1
222 4
333 4
333 4
333 4
444 .
444 1
444 4
444 4
456 1
555 .
555 1
555 4
789 0
999 4
999 1
;
run;
data want;
set have;
by claim;
retain flag_1 flag_4 flag_0 flag_other;
if first.claim
then do;
flag_1 = 0;
flag_4 = 0;
flag_0 = 0;
flag_other = 0;
end;
select (identifier);
when (1) flag_1 + 1;
when (4) flag_4 + 1;
when (0) flag_0 = 1;
otherwise flag_other = 1;
end;
if last.claim
then do;
if flag_4 and not flag_1 then identifier = 4;
else if flag_1
then do;
if flag_4 and not flag_other or flag_4 > 1
then identifier = 0;
else identifier = 1;
end;
else identifier = 0;
if flag_1 + flag_4 + flag_0 then output;
end;
drop flag_:;
run;
proc print data=want noobs;
run;
Result:
claim identifier 222 0 333 4 444 0 456 1 555 1 789 0 999 0
Voila!
Part one is a no-brainer, so I leave that to you.
For part two, what should happen if you have multiple rows for a claim with identical identifiers or where one of the rows has a value other than 1 or 4?
We no need to do anything if we've multiple rows of claims. I just want to know how to convert 1's and 4's for the same claim to 0's.
Typically want to know how to convert two rows to one row (identifier with '0') for the same claim with different identifier (1's and 4's).
Please post the expected output for this dataset:
data have;
input claim identifier;
cards;
123 .
222 1
222 1
222 4
333 4
333 4
333 4
444 .
444 1
444 4
444 4
456 1
555 .
555 1
555 4
789 0
999 4
999 1
;
run;
What happens if you have more than 2 rows with the values 1 or 4?
I need to keep all the claims after the conversion.
Example for the desired output:
claim | Identifier |
123 | . |
456 | 4 |
789 | 1 |
999 | 0 |
999 | 0 |
789 | 1 |
456 | 4 |
@Babloo wrote:
Desired output is,
Claim Identifier
222 0
333 4
444 0
456 1
555 1
789 0
999 0
See this:
data have;
input claim identifier;
cards;
123 .
222 1
222 1
222 4
333 4
333 4
333 4
444 .
444 1
444 4
444 4
456 1
555 .
555 1
555 4
789 0
999 4
999 1
;
run;
data want;
set have;
by claim;
retain flag_1 flag_4 flag_0 flag_other;
if first.claim
then do;
flag_1 = 0;
flag_4 = 0;
flag_0 = 0;
flag_other = 0;
end;
select (identifier);
when (1) flag_1 + 1;
when (4) flag_4 + 1;
when (0) flag_0 = 1;
otherwise flag_other = 1;
end;
if last.claim
then do;
if flag_4 and not flag_1 then identifier = 4;
else if flag_1
then do;
if flag_4 and not flag_other or flag_4 > 1
then identifier = 0;
else identifier = 1;
end;
else identifier = 0;
if flag_1 + flag_4 + flag_0 then output;
end;
drop flag_:;
run;
proc print data=want noobs;
run;
Result:
claim identifier 222 0 333 4 444 0 456 1 555 1 789 0 999 0
Voila!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.