FLAG IF TWO IDS DON'T AGREE

[Cruise]

Hi guys,

Ideally, my patients (pat_id) have same Medicaid_id (mbr_id). One recipient, one Medicaid ID. However, I figured some patients have multiple Medicaid_ids as shown below.

Any hints how to flag patients with multiple Medicaid_ids as shown by 'flag' variable in the code block? Patients can be repeated or occur only once in my data.  

data p.check;
input mbr_id $ pat_id $ flag;
cards;
NZ12345X PAT-12356912 1
NZ12345X PAT-12356912 1
CZ98650P PAT-12356912 1
PL9562QL PAT-98653232 0
PL9562QL PAT-98653232 0
PL9562QL PAT-98653232 0
QM326598 PAT-74123659 1
QM326598 PAT-74123659 1
PL258960 PAT-74123659 1
LP659865 PAT-00235689 0 
;

[novinosrin · Accepted solution]

data check;
input mbr_id : $10. pat_id : $20.;
cards;
NZ12345X PAT-12356912 1
NZ12345X PAT-12356912 1
CZ98650P PAT-12356912 1
PL9562QL PAT-98653232 0
PL9562QL PAT-98653232 0
PL9562QL PAT-98653232 0
QM326598 PAT-74123659 1
QM326598 PAT-74123659 1
PL258960 PAT-74123659 1
LP659865 PAT-00235689 0 
;

proc sql;
create table want as
select *,count(distinct mbr_id)>1 as flag
from check
group by pat_id;
quit;

[novinosrin]

data want;
if 0 then set check;
flag=0;
do until(last.pat_id);
set check;
by pat_id notsorted;
if first.pat_id then _m=mbr_id;
else if mbr_id ne _m then flag=1;
end;
do until(last.pat_id);
set check;
by pat_id notsorted;
output;
end;
drop _:;
run;

[Cruise]

@novinosrin

Thanks a lot. Wow, the disagreement between IDs is worse than I thought. People are having different insurance IDs within just a year and same patient is having 2-4 different IDs. It would have thrown off my analysis had I not assessed like this. Now I know I better stick to patient_id instead Medicaid_id.

Thanks again. I find proc sql easier to digest and less number of rows. Sure both works.

[novinosrin]

Jeez, That sounds like a poor design of database entities in the first place allowing different insurance ID's for the same person within a year. Well! you know best!

[Cruise]

Yes, but I used mbr_id as a linkage variable to link three Medicaid files together where Medicaid_id within Medicaid data would show better agreement than to external data which is cancer registry where patient_id comes from. using mbr_id just to link the datasets separated due to storage space issue. Now using patient_id as a unit of my study.

[Cruise]

I guess, people tend to upgrade their plan when cancer treatment gets more intensive down the road.

[novinosrin]

@Cruise Hmm that's some interesting domain. Thank you for sharing. These knowledge adds substance to the thread making it more interesting and intuitive. Cheers!