DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 41

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

[ Edited ]

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... 

PROC Star
Posts: 597

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

[ Edited ]
/*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;

 

Contributor
Posts: 41

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

Posted in reply to novinosrin
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
PROC Star
Posts: 597

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

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

Contributor
Posts: 41

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

Posted in reply to novinosrin
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!
PROC Star
Posts: 597

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

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

Contributor
Posts: 41

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

[ Edited ]
Posted in reply to novinosrin

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... 

PROC Star
Posts: 597

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


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;

Contributor
Posts: 41

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

Posted in reply to novinosrin
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)
PROC Star
Posts: 597

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

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

Trusted Advisor
Posts: 1,118

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

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:

  1. The "if last.enrolid" subsetting if allow calculation of range between min_date and max svcdate.
  2. 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.
  3. This program assume each id is physically grouped by enrolid, although not necessarily in ascending or descending order.
Super User
Super User
Posts: 7,254

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

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;
Super User
Posts: 10,200

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

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;
Ask a Question
Discussion stats
  • 12 replies
  • 128 views
  • 0 likes
  • 5 in conversation