How to identify a change across observations and flag it to create a new variable

[shasank]

Hi Reeza,

Thank you for your reply. I did anticipate this situation which is why I have posted this code with a new similar dataset and not the old one. The code seems to be working fine with the old dataset but this not with new similar data set.

[novinosrin]

@shasank I'm afraid Mon, Tue and Wed are very busy with my full time classes so I can only help perhaps on Thu. I will try to modify if I get some long breaks in between classes. You might wonder then how come I participate in the forum if I have classes. And that's only because if it's too easy i tend to jump in. 

[shasank]

That's Awesome. Thank you for your help.

[Tom]

---

@shasank wrote:
Hi Reeza,
That was a typo. This is the syntax I used.
data firstchem;
set b;





by ID Drug;
if first.id and First.drug then output;
Firstdate = startdate;
drop startdate;
run;
Yet I get same missing

---

Your use of the FIRST. variables does not look correct.  Note that FIRST.ID will be true only once per ID value.  Also note that FIRST.DRUG will always be true when FIRST.ID is true. 

Also why are you writing the observation before setting the value for FIRSTDATE?

Did you mean something like this?

data firstchem;
  set b;
  by ID Drug;
  if first.drug ;
  keep id drug startdate;
  rename startdate=Firstdate ;
run;

[PGStats]

This would seem to adhere to your requirements, even if the results differ slightly from prescribed:

data have;
input PID StartDate :anydtdte. Drug $;
format startDate yymmdd10.;
datalines; 
1 9-May-12 A 
1 10-May-12 A 
1 10-May-12 B 
1 30-May-12 B 
1 31-May-12 A 
1 1-Jun-12 A 
1 2-Jun-12 B 
1 30-Jun-12 C 
1 3-Jul-12 A 
1 27-Jul-12 A 
1 28-Jul-12 D 
2 17-May-12 A 
2 22-May-12 B 
2 25-May-12 B 
2 1-Jun-12 C 
2 2-Jun-12 D 
2 3-Jun-12 B 
3 26-Jun-12 A 
3 29-Jun-12 A 
3 27-Jul-12 B 
3 25-Aug-12 B 
4 26-Jun-12 A 
4 27-Jun-12 B 
4 28-Jun-12 C 
4 25-Aug-12 A 
4 26-Aug-12 B 
4 27-Aug-12 B 
;

proc sql;
create table c as
select
    a.pid, a.startDate,
    b.drug
from
    have as a inner join
    have as b on a.pid=b.pid and 
        intck("day", a.startDate, b.startDate) between -3 and 10
order by pid, startDate, drug;
quit;

data d;
length regimen $100;
do until(last.startDate);
    set c; by pid startDate drug;
    if first.drug then regimen = catx("+", regimen, drug);
    end;
drop drug;
run;

proc sort data=d out=e nodupkey; by pid regimen; run;

proc transpose data=e out=want(drop=_name_) prefix=regimen;
by pid;
var regimen;
run;

proc print data=want noobs; run;

                      PID    regimen1    regimen2    regimen3

                       1       A+B        A+C         A+D
                       2       A+B        B+C         B+C+D
                       3       A          B
                       4       A+B        A+B+C

[shasank]

Hi @PG stats,
Thank you for your valuable time and expertise.
Your solution matched output for PID 1 and 3 but not for 2 and 4.
for PID 2 the Regimen 1 is 1st drug + drugs given in 10 days = A + B.
Regimen 2 creation should be initiated by presence of a drug not A or B (I:e not in Regimen1) . C was started on 1-Jun-12. So, Surveillance period start is Newdrug - 3 to newdrug + 10 - 27-May-12 to 11-Jun-12. The drugs given in this period were B+C+D. As there are no drugs given beyond this point Regimen3 should be missing.
The logic is to only create a new regimen when there a new drug given to the person which is not in Regimen(i)-1 or the previous regimen. Hop[e this makes sense.

[novinosrin]

Please clarify

Your wrote:

Next, Scan for other drugs given to person 1 and if any drug other than A or B (Regimen1). Under person 1 Drug C was given on 30/Jun/12 which is not in Regimen1. 

If this is the case, how does B qualify for regimen 2 while it is in regimen 1 in 

2

A+B

B+C+D

[shasank]

Hi @novinosrin,
Thank you for working on this.
The meaning of aRegimen is a combination of drugs given to a patient in an interval of time.
So, coming to the question - Regimen 2 creation should trigger when the person received a drug not in regimen 1. In the case of Person 2, He received A and B in regimen 1(1st drug startdate+ 10days) and Drug C was given on 1-Jun-12 which is a trigger for Regimen 2 creation. As we have a trigger now the interval of Regimen2 is Drug C startdate minus 3 till DrugC startdate +10 . The drugs given in this interval are B C and D. Drug B was given on 3-Jun-12 so as it is within the interval of Regimen2 it should be added to the regimen.
I hope I answered your question.

[shasank]

The main key is the identification of Regimen intervals to create regimens.

Regimen1 interval is Patient's 1st drug startdate + 10days -  All the drugs given in this period is Regimen 1

Regimen2 Interval is the Drug not in Regimen1 Startdate  - 3 till +10 days. All the drug given in this period is Regimen 2

Regimen3 Interval is the not in Regimen1 New drug Startdate  - 3 till +10 days. All the drug given in this period is Regimen 3

Regimen4 Interval is the not in Regimen1 New drug Startdate  - 3 till +10 days. All the drug given in this period is Regimen 4

and so on so forth.

Am i clear?

[novinosrin · Accepted solution]

@shasank Try this and let me know

data have;

    input PID StartDate :anydtdte. Drug $;

    format startDate yymmdd10.;

    datalines;

1 10MAY2012 arb

1 10MAY2012 acl

1 17MAY2012 arb

1 17MAY2012 acl

1 30MAY2012 rlo

1 30MAY2012 rlo

1 30MAY2012 rlo

1 26JUN2012 rlo

1 19JUL2012 rlo

1 27AUG2012 rlo

1 25SEP2012 rlo

2 19FEB2008 eva

2 19FEB2008 eva

2 19FEB2008 arb

2 19FEB2008 arb

2 19FEB2008 emc

2 28FEB2010 ymc

3 09NOV2011 isp

3 27NOV2011 emc

;

run;

proc sql;
select count(distinct Drug) into : reg_limit TRIMMED
from have;
quit;

data want;

if _N_ = 1 then do;

      if 0 then set have;

    declare hash h(dataset: "have",multidata: 'y');

    h.defineKey('pid');

    h.defineData('StartDate','drug');

    h.defineDone();

 end;

do until(last.pid);

      set have(rename=(startdate=_startdate drug=_drug));

      by pid;

      array r(*) $100 reg1-reg&reg_limit;

if first.pid then

            do;

                  call missing(of r(*));

                  _count=1;

                  r(_count)=_drug;

                  do while(h.do_over(key: pid) eq 0);

                  if    intck('day', _startdate, startdate)<=10 and findw(r(_count), strip(drug))=0  then

                        do;                          

                              r(_count)=catx('+', r(_count), drug);                      

                        end;

                  end;

            end;

else do;

      if findw(r(_count), strip(_drug))=0 then

            do;

                  _count+1;

                  r(_count)=_drug;

                  do while(h.do_over(key: pid) eq 0); 

                  if intnx('day', _startdate, -3)<=startdate<=intnx('day', _startdate, 10) and findw(r(_count), strip(drug))=0 then

                        do;

                              r(_count)=catx('+', r(_count), drug);

                        end;

                  end;

            end;

      end;

end;

keep pid reg:;

run;

[novinosrin]

@shasank  I am assuming you are working in a 9.4 environment

[shasank]

Yes, I have SAS 9.4. Is that a good thing or bad thing.?

[novinosrin]

Well, my code would only work in 9.4. So if you are using mine, i suppose it's a good thing