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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: