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
;
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;
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;
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;
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.
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 Hmm that's some interesting domain. Thank you for sharing. These knowledge adds substance to the thread making it more interesting and intuitive. Cheers!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.