turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Re: Identifying enrolids with two claims for speci...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2 weeks ago - last edited 2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago - last edited 2 weeks ago

```
/*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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

2 weeks ago

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

2 weeks ago - last edited 2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

2 weeks ago

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)

(Will try once I get home)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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;
```

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

Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cdubs

2 weeks ago

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;
```