Solved
New Contributor
Posts: 3

# create drug regimen

Hello there,

I need help to create drug regimen with claims data and the definition is a little complicated. Let me have an example first:

Here is the example data set I have:

ID     drug     refill_dt     supply_day  end(=refill_dt+supply_day+7)

1     1          5/1/08          30               6/7/08

1     2          5/10/08          15               6/1/08

1     3          5/13/08          30               6/19/08

1     4          6/15/08          15               7/7/08

I'm look for if any drugs refill or prescription in the same period and allow for 7 days as a gap, so here, drug1 and drug 2 have overlap in part of the period then they are one of the regimen that this ID used. For the drug 3, the period time is from 5/13/08 to 6/12/08. Since we allow for 7 days as a gap, drug 3 and drug 4 together is one of the regimen. For the period that there is only one drug, that drug is one of the regimen. It's like we look at the whole history of drug prescription and split out the periods by any drug start or end.

Here is the data set I want:

ID     regimen     new_start     new_end

1     1               5/1/08          5/9/08

1     1,2             5/10/08         5/12/08

1     1,2,3          5/13/08          6/1/08

1     1,3             6/2/08             6/7/08

1     3               6/8/08               6/14/08

1     3,4              6/15/08          6/19/08

1     4               6/20/08              7/7/08

I really appreciate for any help!

Thanks!

Accepted Solutions
Solution
‎05-22-2014 10:03 PM
Posts: 5,523

## Re: create drug regimen

This, I believe, is a fairly efficient way to do this:

data claims;

input ID drug rdt :mmddyy8. dur;

format rdt yymmdd10.;

datalines;

1     1          5/1/08          30

1     2          5/10/08          15

1     3          5/13/08          30

1     4          6/15/08          15

;

data rbegs;

set claims;

rBeg = rdt;

output;

rBeg = intnx("DAY", rdt, dur+8);

output;

format rBeg yymmdd10.;

keep ID rBeg;

run;

proc sql;

create table drugBegs as

select R.ID, C.drug, C.rdt, R.rBeg

from rBegs as R left join claims as C

on R.ID=C.ID and R.rBeg between C.rdt and intnx("DAY", C.rdt, C.dur+7)

order by ID, rBeg, drug, rdt ;

quit;

data drugLists;

length list \$32;

do until(last.rBeg);

set drugBegs; by ID rBeg;

list = catx(",", list, drug);

end;

if first.ID then regimenID = 0; /* if regimenID starts at 1 for every ID */

regimenID + 1;

output;

run;

data regimen;

set drugLists end=done;

if not done then set drugLists(keep=rBeg rename=rBeg=next firstobs=2);

if not missing(rdt) then do;

rEnd = intnx("DAY", next, -1);

output;

end;

format rEnd yymmdd10.;

drop drug rdt next;

run;

proc print data=regimen noobs; var ID regimenID list rBeg rEnd; run;

PG

PG

All Replies
Solution
‎05-22-2014 10:03 PM
Posts: 5,523

## Re: create drug regimen

This, I believe, is a fairly efficient way to do this:

data claims;

input ID drug rdt :mmddyy8. dur;

format rdt yymmdd10.;

datalines;

1     1          5/1/08          30

1     2          5/10/08          15

1     3          5/13/08          30

1     4          6/15/08          15

;

data rbegs;

set claims;

rBeg = rdt;

output;

rBeg = intnx("DAY", rdt, dur+8);

output;

format rBeg yymmdd10.;

keep ID rBeg;

run;

proc sql;

create table drugBegs as

select R.ID, C.drug, C.rdt, R.rBeg

from rBegs as R left join claims as C

on R.ID=C.ID and R.rBeg between C.rdt and intnx("DAY", C.rdt, C.dur+7)

order by ID, rBeg, drug, rdt ;

quit;

data drugLists;

length list \$32;

do until(last.rBeg);

set drugBegs; by ID rBeg;

list = catx(",", list, drug);

end;

if first.ID then regimenID = 0; /* if regimenID starts at 1 for every ID */

regimenID + 1;

output;

run;

data regimen;

set drugLists end=done;

if not done then set drugLists(keep=rBeg rename=rBeg=next firstobs=2);

if not missing(rdt) then do;

rEnd = intnx("DAY", next, -1);

output;

end;

format rEnd yymmdd10.;

drop drug rdt next;

run;

proc print data=regimen noobs; var ID regimenID list rBeg rEnd; run;

PG

PG
New Contributor
Posts: 3

## Re: create drug regimen

Thanks so much PG for a quick help with that! Also I'm so glad you replied on it since I learned a lot from you in the forum! The sql part really smart and that's something I didn't know before. For the part you used do loop before set statement, would you explain a little bit on that? How come concatenate all the drug during the period together?

Thanks so much!

Posts: 5,523

## Re: create drug regimen

The "DO loop before SET statement" has become a classic of advanced SAS programming. It is called the DOW loop. Search for DOW on SAS sites, including this Forum, for more examples. It is a useful construct for the treatment of BY groups within a datastep. The not so obvious aspect of the DOW loop technique is the reference to the last.something automatic variable before it is implicitly declared in the BY statement. It works because the UNTIL clause is evaluated at the end of the loop, after the SET statement is executed.

PG

PG
Posts: 3,852

## Re: create drug regimen

Another perhaps interesting or irrelevant tidbit is that you can create first and last variables with assignment statement in data steps that don't even have BY.  You cannot define them with length or array.  You can define  an array of them after BY statement

data _null_;
last.age=
8;
first.hello=
20;

put _all_;

run;

LAST.AGE=
8 FIRST.HELLO=20 _ERROR_=0 _N_=1
Posts: 3,167

## Re: create drug regimen

While I can't comment on the efficiency, Here is another one of doing it (adding another ID for multiple ID testing purpose)

data have;

input (ID     drug) (:\$)     refill_dt:mmddyy10.     supply_day end:mmddyy10.;

format refill_dt end mmddyy10.;

cards;

1     1 5/1/08          30               6/7/08

1     2 5/10/08          15               6/1/08

1     3 5/13/08          30               6/19/08

1     4 6/15/08          15               7/7/08

2     1 5/1/08          30               6/7/08

2     2 5/10/08          15               6/1/08

2     3 5/10/08          30               6/14/08

2     4 6/15/08          15               7/7/08

;

data want;

if _n_=1 then do;

declare hash h1(ordered: 'a');

h1.definekey('date');

h1.definedata('date', 'type');

h1.definedone();

declare hiter h1t('h1');

declare hash h2(multidata:'y');

h2.definekey('date');

h2.definedata('Drug','date');

h2.definedone();

declare hash h3(ordered:'a');

h3.definekey('Drug');

h3.definedata('Drug');

h3.definedone();

declare hiter h3t('h3');

end;

do until (last.id);

set have;

by id notsorted;

length regimen \$ 100;

format new_start new_end mmddyy10.;

date=refill_dt; type='s'; rc1=h1.replace();

date=end; type='e';rc1=h1.replace();

do date=refill_dt to end;

end;

end;

rc1=h1t.first();

new_end=ifn(type='s',date-1,date);

do while (rc1=0);

new_start=new_end+1;

rc1=h1t.next();

new_end=ifn(type='s',date-1,date);

do date=new_start to new_end;

do rc2=h2.find() by 0 while (rc2=0);

rc3=h3.replace();

rc2=h2.find_next();

end;

end;

do rc3=h3t.first() by 0 while (rc3=0);

regimen=catx(',', regimen,drug);

rc3=h3t.next();

end;

if not missing (regimen) then output;

rc3=h3.clear();

call missing(regimen);

end;

rc1=h1.clear(); rc2=h2.clear();

keep id regimen new_start new_end;

run;

Haikuo

New Contributor
Posts: 3

## Re: create drug regimen

Thanks Haikuo! Your code is way above my ability You are awesome! Thanks again for your help!

Posts: 3,852

## Re: create drug regimen

What happened to your indention?  It went wacky at the BY statement.  Do you use real tabs in your editor?

Posts: 3,167

## Re: create drug regimen

Apologize to all. It puzzled me as well, and this was not the first time. I did the code in SAS editor, It looked fine,  then copied it to MS Word, still looked fine,  then train wrecked here. No, I did not use Tabs.

Haikuo

Posts: 3,852

## Re: create drug regimen

I use COPY as HTML from EG

The switch form edit to HTML

and paste the HTML

I'ts kind of a pain but it preserves the formatting from the editor in EG.

PROC Star
Posts: 1,457

## Re: create drug regimen

Switching the form edit to HTML is the only way I can paste code at all (using IE8, chrome works better).

I tend to switch to HTML, then just paste my code into a <pre> </pre> block.  Usually works okay.

Super User
Posts: 10,768

## Re: create drug regimen

If I understand what you mean.

```data claims;
input ID drug rdt :mmddyy8. dur;
format rdt yymmdd10.;
datalines;
1     1          5/1/08          30
1     2          5/10/08          15
1     3          5/13/08          30
1     4          6/15/08          15
;
run;
data temp(keep=id drug date);
set claims;
do date=rdt to rdt+dur+7;
output;
end;
format date date9.;
run;
proc sql noprint;
select distinct catt('temp(where=(id=',put(id,best8. -l),' and drug_',put(drug,best8. -l),'=',put(drug,best8. -l),') rename=(drug=drug_',put(drug,best8. -l),') )') into : list separated by ' ' from temp;
quit;
options missing=' ';
data temp1(drop=drug_:);
merge &list ;
by id date;
reg=catx(',',of drug_:);
run;
data want;
set temp1;
by id reg;
retain new_start .;
if first.reg then new_start=date;
if last.reg then do;new_end=date;output;end;
format new_: date9.;
drop date;
run;

```

Xia Keshan

🔒 This topic is solved and locked.