Hello,
I am having some trouble working with my data. Hoping to get some help from the community.
Context:
In the original table below, starting with row #2 'svcdate_shifted' is computed as 'svcdate_shifted + Daysupp' from previous row. For row #1 -> svcdate_old = svcdate_shifted
Original Table1
Patient
ProdNme
Svcdate_old
Scvdate_shifted
Daysupp
B
BupA
9/29/14
9/29/14
7
B
BupA
10/6/14
10/6/14
30
B
Nal
10/9/14
11/5/14
30
B
Nal
10/16/14
12/5/14
5
B
Nal
10/31/14
12/10/14
30
B
BupA
11/18/14
1/9/15
7
B
BupA
11/24/14
1/16/15
30
B
Nal
12/24/14
2/15/15
30
I want to create the New Table below by manipulating data as follows:
When we encounter a change in prescription (BUP to Nal or vice versa) , if svcdate_old for Nal row < svcdate_shifted + daysupp (from previous row), then we recompute DaySupp (for the previous row) as Svcdate_old (from Nal row) - svcdate_shifted (from previous row). Hence the DaySupp in this New Table is now 3 for row #2 and is 5 for row #5
The Svcdate_shifted continues to be computed as 'svcdate_shifted + Daysupp' from previous row
Big picture: Any time the patient gets a new prescription (I.e BUP or Nal) represented by svcdate_old, we assume that they stop taking the previous prescription (and start with the new prescription) and hence the DaySupp value needs to be changed to represent that switch
New Table1
Patient
ProdNme
Svcdate_old
Scvdate_shifted
Daysupp
B
BupA
9/29/14
9/29/14
7
B
BupA
10/6/14
10/6/14
3
B
Nal
10/9/14
10/9/14
30
B
Nal
10/16/14
11/8/14
5
B
Nal
10/31/14
11/13/14
5
B
BupA
11/18/14
11/18/14
7
B
BupA
11/24/14
11/25/14
30
B
Nal
12/26/14
12/25/14
30
Just to present a different order of data, look at Original Table2 is as below:
Original Table2
Patient
ProdNme
Svcdate_old
Scvdate_shifted
Daysupp
B
BupA
9/29/14
9/29/14
7
B
BupA
10/6/14
10/6/14
30
B
Nal
10/9/14
11/5/14
30
B
BupA
10/4/14
12/5/14
6
B
Nal
10/16/14
12/11/14
5
B
Nal
10/31/14
12/16/14
30
B
BupA
11/18/14
1/15/15
7
B
BupA
11/24/14
1/22/15
30
B
Nal
12/24/14
2/21/15
30
I want to recreate new table 2 as follows with the same criteria that I listed in the earlier part of the post
New Table2
Patient
ProdNme
Svcdate_old
Scvdate_shifted
Daysupp
B
BupA
9/29/14
9/29/14
7
B
BupA
10/6/14
10/6/14
3
B
Nal
10/9/14
10/9/14
2
B
BupA
10/11/14
10/11/14
5
B
Nal
10/16/14
10/16/14
5
B
Nal
10/31/14
10/21/14
28
B
BupA
11/18/14
11/18/14
7
B
BupA
11/24/14
11/25/14
29
B
Nal
12/24/14
12/24/14
30
I originally had the below code, which worked when I only had 1 Nal visit & I purposefully stopped counting at that visit (I deleted all observations after this first Nal visit). However, I need to amend this code to now consider the different scenario above.
%macrohop(d,x,y,j);
_p_ = _n_ + &j;
if (1le _p_ le _o_) then set &d(keep=&x rename=(&x=&y)) point=_p_ nobs=_o_;
%mendhop;
data pers_3M_12M_30DAY (drop=TEMP1);
set pers_3M_12M_30DAY;
if prodnme = "NAL"
and svcdate_new le lag(svcdate_new)+ lag(daysupp)
thendo;
%hop(pers_3M_12M_30DAY,SVCDATE_NEW, TEMP1,-1)
DAYSUPP_NEW= SVCDATE-TEMP1;
end;
run;
%MACROHop1(d, x, y, j);
_p_=_n_ + &j;
IF(1<= _p_ <= _o_)THEN
DO;
_Found=1;
SET&d(KEEP=&x RENAME=(&x=&y)) POINT=_p_ NOBS=_o_;
END;
ELSE
DO;
_Found=0;
END;
%MENDHop1;
datapers_3M_12M_30DAY ;
setpers_3M_12M_30DAY;
%Hop1(pers_3M_12M_30DAY, ProdNme, _Next_ProdNme, 1);
IF_FoundTHEN
IF_Next_ProdNme = "NAL"
THEN
%Hop1(pers_3M_12M_30DAY, DAYSUPP_NEW, _Next_DAYSUPP_NEW, 1);
IF_Next_DAYSUPP_NEW ne .thendo;
daysupp = _Next_DAYSUPP_NEW;
drop_:;
end;
run;
... View more
