I have a data set that monitors a member ID and their enrollment dates. There are scenarios where a member can have enrollment in two different C_ID's. I'm trying to summarize this scenario and capture the MEMID's only that fall into this issue. I've tried a few SQL statements with count(*) as rcrds and grouping by MEMID & C_ID's but for some reason I can't figure this out.
proc sql;
create table TEST (MEMID num, CLNDR_CY_MO_NUM num, C_ID char(3));
insert into test
values(123, 201912, 'H41')
values(123, 202001, 'H41')
values(123, 202002, 'H41')
values(123, 202003, 'H41')
values(123, 202004, 'H41')
values(123, 202005, 'H41')
values(123, 202006, 'H41')
values(123, 202007, 'H05')
values(123, 202007, 'H41')
values(124, 201912, 'H51')
values(124, 202001, 'H51')
values(124, 202002, 'H51')
values(124, 202003, 'H51')
values(124, 202004, 'H51')
;
run;
/* I only want to see MEMID's who have two c_ID's for the same CLNDR_CY_MO_NUM */
proc sql;
create table want (MEMID num);
insert into want
values(123);
run;
Logic I need is where C_ID not = C_ID but CLNDR_CY_MO_NUM = CLND_CY_MO_NUM by MEMID
I want to be able to summarize / identify all MEMID's that fall into this scenario
... View more
