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...
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;
/*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;
Sorry I am unable to picture or imagine your data and what's coming
Provide us a sample of what you HAVE and what you WANT please
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...
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;
yes you may have to add a where or IF, depending on the dx code to satisfy and process
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:
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.