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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

13 REPLIES 13
novinosrin
Tourmaline | Level 20
/*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;

 

cdubs
Quartz | Level 8
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
novinosrin
Tourmaline | Level 20

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

cdubs
Quartz | Level 8
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!
novinosrin
Tourmaline | Level 20

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

cdubs
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20


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;

cdubs
Quartz | Level 8
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)
novinosrin
Tourmaline | Level 20

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

mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

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;
cdubs
Quartz | Level 8
Thank you! This works well! I modified slightly but the concept is what I ended up using 🙂
Ksharp
Super User

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;

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
  • 13 replies
  • 1599 views
  • 2 likes
  • 5 in conversation