BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cruise
Ammonite | Level 13

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 
;
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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
Tourmaline | Level 20
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
Ammonite | Level 13

@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
Tourmaline | Level 20

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
Ammonite | Level 13
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
Ammonite | Level 13
I guess, people tend to upgrade their plan when cancer treatment gets more intensive down the road.
novinosrin
Tourmaline | Level 20

@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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 848 views
  • 3 likes
  • 2 in conversation