topic Re: Identifying enrolids with two claims for specific dx code, at least one month apart in SAS Programming

Identifying enrolids with two claims for specific dx code, at least one month apart?

cdubs — Tue, 02 Jan 2018 20:56:12 GMT

Hi,

I am selecting a sample of enrolid from insurance data that has two claims for a set of dx codes, and the claims are at least one month apart.

I was thinking of something along the lines of

%let dx_list='1234', '1356';

/*First step*/
data flag_dx; 
set have;
if (dx1 in &dx_list. or dx2 in &dx_list.) then dx_flag=1;
run;

/*Second step (not sure how to translate this into SAS)*/
data flag_month_apart;
set flag_dx;
if flag=1 and (intck ('month', svc_date, next obs where flag=1)>=1) then do;
month_flag=1;
end;
run;

/*Third step (generate list of enrolids with month_flag=1)*/
proc sort data=flag_month_apart nodupkey;
by enrolid; where month_flag=1; 
run;

/*Fourth step (inner join w/ final dataset)*/
proc sql; 
create table final_patients as 
select a.*, b.*
from have as a 
flag_month_apart as b
where a.enrolid = b.enrolid;
quit;

Not sure if people have a better strategy, and/or have suggestions as to how to translate some of the above into SAS?

Thank you!

UPDATE: -- example of what I have and want.

Have:

enrolid dx1 dx2 svcdate

839571 1234 8593 8/11/2010

839571 1234 8593 8/23/2010

839571 1234 8593 9/15/2010

958170 9381 1935 7/25/2012

958170 1234 8311 9/30/2012

553312 8311 1234 7/15/2012

553312 8311 1234 7/21/2012

Want

enrolid dx1 dx2 svcdate flag_dx flag_month_apart

839571 1234 8593 8/11/2010 1 .

839571 1234 8593 8/23/2010 1 .

839571 1234 8593 9/15/2010 1 1

Because the other ones don't have two claims for the right dx codes at least one month apart.

I want only the enrolids (and all the observations associated with them), as long as the enrolid has two+ qualifying dx codes at least one month apart...

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

novinosrin — Tue, 02 Jan 2018 20:13:28 GMT

/*Second step (not sure how to translate this into SAS)*/
data flag_month_apart;
set flag_dx;
if flag=1 and (intck ('month', svc_date, next obs where flag=1)>=1) then do;
month_flag=1;end;
run;

is next obs name of a variable?

your req is not clear:

but is this what you want?

data flag_month_apart;
set flag_dx;
if flag=1 and intck ('month', svc_date, nextobs)>=1 then month_flag=1;
run;

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

cdubs — Tue, 02 Jan 2018 20:13:56 GMT

Sorry that’s the part I think I need help with translating into SAS. the code is supposed to refer us to the next observation where dx_flag =1

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

novinosrin — Tue, 02 Jan 2018 20:18:29 GMT

Sorry I am unable to picture or imagine your data and what's coming

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

cdubs — Tue, 02 Jan 2018 20:21:56 GMT

Sorry it’s not clear! The substitution code you work is closer to what I
think I may want. Is nextobs a SAS function? Basically want to say to SAS
“find me the next observation where the flag =1 and tell me if it’s more
than a month apart”.

Thanks!

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

novinosrin — Tue, 02 Jan 2018 20:23:20 GMT

Provide us a sample of what you HAVE and what you WANT please

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

cdubs — Tue, 02 Jan 2018 20:53:55 GMT

Great idea! I should have done that.

Say that

%let dx_list='1234', '1356';

Have:

enrolid dx1 dx2 svcdate

839571 1234 8593 8/11/2010

839571 1234 8593 8/23/2010

839571 1234 8593 9/15/2010

958170 9381 1935 7/25/2012

958170 1234 8311 9/30/2012

553312 8311 1234 7/15/2012

553312 8311 1234 7/21/2012

Want

enrolid dx1 dx2 svcdate flag_dx flag_month_apart

839571 1234 8593 8/11/2010 1 .

839571 1234 8593 8/23/2010 1 .

839571 1234 8593 9/15/2010 1 1

Because the other ones don't have two claims for the right dx codes at least one month apart.

I want only the enrolids (and all the observations associated with them), as long as the enrolid has two+ qualifying dx codes at least one month apart...

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

novinosrin — Tue, 02 Jan 2018 21:00:01 GMT

data have;
input enrolid dx1 dx2 svcdate :mmddyy10.;
format svcdate mmddyy10.;
datalines;
839571 1234 8593 8/11/2010
839571 1234 8593 8/23/2010
839571 1234 8593 9/15/2010
958170 9381 1935 7/25/2012
958170 1234 8311 9/30/2012
553312 8311 1234 7/15/2012
553312 8311 1234 7/21/2012
;
data want;
set have;
by enrolid notsorted;
k=lag(svcdate);
if not first.enrolid and intck('month',k,svcdate)>=1 then month_flag=1;
drop k;
run;

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

cdubs — Tue, 02 Jan 2018 21:53:04 GMT

Thank you! And if I want to make sure the service date is only counted if the Dx code is correct, do I add a “where dx1 in &dx_list. ...” or something like that?

(Will try once I get home)

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

novinosrin — Tue, 02 Jan 2018 21:54:09 GMT

yes you may have to add a where or IF, depending on the dx code to satisfy and process

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

Tom — Tue, 02 Jan 2018 22:44:57 GMT

Sounds like you could just take the min and max date and check if they are at least 30 days apart.

proc sql ;
create table want as
select enrolid
     , min(svc_date) as firstdt format=date9.
     , max(svc_date) as lastdt format=date9.
     , count(distinct svc_date) as ndates
     , count(*) as nrecords
where (dx1 in &dx_list. or dx2 in &dx_list.)
group by 1
having (calculated lastdt - calculated firstdt) > 30
;
quit;

Re: Identifying enrolids with two claims for specific dx code, at least one month apart

mkeintz — Wed, 03 Jan 2018 02:23:50 GMT

Like so many questions in sas community, this task can be based on choosing the right type of self-interleaving. I.e. you can pass through each ID twice, once to establish the filter (max_date-min_date>=1 month for qualifying DX codes), and a second time to reread, calculate individual flag values, and optionally output:

data Have;
 input enrolid dx1 :$4. dx2 :$4. svcdate :mmddyy10.;
 format svcdate yymmddn8.;
datalines;
839571 1234 8593 8/11/2010 
839571 1234 8593 8/23/2010 
839571 1234 8593 9/15/2010 
958170 9381 1935 7/25/2012 
958170 1234 8311 9/30/2012 
553312 8311 1234 7/15/2012 
553312 8311 1234 7/21/2012 
run;

%let dx_list='1234', '1356';

data want (drop=range min_date);
  set have ;
  by enrolid notsorted;
  where dx1 in (&dx_list) or dx2 in (&dx_list);
  if first.enrolid then min_date=svcdate;
  retain min_date;

  if last.enrolid;
  range=intck('month',min_date,svcdate,'C');

  do until (last.enrolid);
    set have;
    by enrolid notsorted;
    flag=(1<= intck('month',min_date,svcdate,'C'));
    if range>=1 then output;
  end;
run;

Notes:

The "if last.enrolid" subsetting if allow calculation of range between min_date and max svcdate.
After the "if last.enrolid" and range calculation is a do loop, which re-reads the same enrolid group and generate a record-by-record value for flag. If range satisfies the filter the record is output.
This program assume each id is physically grouped by enrolid, although not necessarily in ascending or descending order.

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

Ksharp — Wed, 03 Jan 2018 13:22:44 GMT

There are too many things uncertainty. If I understood correctly.

data Have;
 input enrolid dx1 :$4. dx2 :$4. svcdate :mmddyy10.;
 format svcdate yymmddn8.;
datalines;
839571 1234 8593 8/11/2010 
839571 1234 8593 8/23/2010 
839571 1234 8593 9/15/2010 
958170 9381 1935 7/25/2012 
958170 1234 8311 9/30/2012 
553312 8311 1234 7/15/2012 
553312 8311 1234 7/21/2012 
run;

%let dx_list='1234', '1356';
proc sql;
create table id as
 select distinct enrolid from have
  where dx1 in (&dx_list)
   group by enrolid,dx1
    having intck('month',min(svcdate),max(svcdate),'c')>0
union
 select distinct enrolid from have
  where dx2 in (&dx_list)
   group by enrolid,dx2
    having intck('month',min(svcdate),max(svcdate),'c')>0 ;

create table want as
 select *
  from have
   where enrolid in (select enrolid from id);
quit;

Re: Identifying enrolids with two claims for specific dx code, at least one month apart?

cdubs — Fri, 26 Jan 2018 23:50:20 GMT

Thank you! This works well! I modified slightly but the concept is what I ended up using 🙂