In my dataset, I need to compute the variable cycle_day(in the desired output below). The data will have v_1 to v_n and d_1 to d_n. Depending upon my 'visit' and where the 'day' falls in relation to the first day of cycle will determine the variable 'cycle_day'. The cycle_day formula will be (day - d_n + 1). For example, the first record has C1, so day has to be compared with d_1 and then 1- 1 +1 = 1. So cycle_day = 1. the 4th record has C2, so day has to be compared with d_2 and then 22-22 + 1 = 1. So cycle_day = 1 the 7th record has C1, so day has to be compared with d_1 and then 3-1 +1 = 3. So cycle_day = 3 I am finding it tricky to account for all possible v_n and d_n. Any suggestions? data have;
input subject visit day v_1 d_1 v_2 d_2 v_3 d_3 v_4 d_4;
datalines;
12345 C1 1 C1D1 1 C2D1 22 C3D1 43 .
12345 C1 2 C1D1 1 C2D1 22 C3D1 43 .
12345 C1 10 C1D1 1 C2D1 22 C3D1 43 .
12345 C2 22 C1D1 1 C2D1 22 C3D1 43 .
12345 C3 43 C1D1 1 C2D1 22 C3D1 43 .
23456 C1 1 C1D1 1 C2D1 22 C3D1 51 C4D1 72
23456 C1 3 C1D1 1 C2D1 22 C3D1 51 C4D1 72
23456 C2 22 C1D1 1 C2D1 22 C3D1 51 C4D1 72
23456 C3 51 C1D1 1 C2D1 22 C3D1 51 C4D1 72
23456 C4 72 C1D1 1 C2D1 22 C3D1 51 C4D1 72
run; Desired output : subject visit day v_1 d_1 v_2 d_2 v_3 d_3 v_4 d_4 v_n d_n cycle_day 12345 C1 1 C1D1 1 C2D1 22 C3D1 43 . 1 12345 C1 2 C1D1 1 C2D1 22 C3D1 43 . 2 12345 C1 10 C1D1 1 C2D1 22 C3D1 43 . 10 12345 C2 22 C1D1 1 C2D1 22 C3D1 43 . 1 12345 C3 43 C1D1 1 C2D1 22 C3D1 43 . 1 23456 C1 1 C1D1 1 C2D1 22 C3D1 51 C4D1 72 1 23456 C1 3 C1D1 1 C2D1 22 C3D1 51 C4D1 72 3 23456 C2 22 C1D1 1 C2D1 22 C3D1 51 C4D1 72 1 23456 C3 51 C1D1 1 C2D1 22 C3D1 51 C4D1 72 1 23456 C4 72 C1D1 1 C2D1 22 C3D1 51 C4D1 72 1
... View more
