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 |
Hi @gpv2000 I modified the previous and did you test with that? here it is again .
data want;
set have;
array t(*) d_:;
cycle_day=day- t(input(substr(visit,2),8.))+1;
run;
Does v_1 ever contain something else than "C1D1", does v_2 ever contain something else than "C2D1", etc?
That dataset is a result of transpose procedure and the values has such will not change. V1 will either be missing or c1d1.
data have;
infile cards truncover;
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
;
data want;
set have;
array t(*) d_:;
cycle_day=day- t(input(char(visit,2),1.))+1;
run;
It works except for the cases when cycle is greater than 9.
Please post a better and more comprehensive sample that would help test/modify at best
Oh well, might be just a simple change to substr from char perhaps
data want;
set have;
array t(*) d_:;
cycle_day=day- t(input(substr(visit,2),8.))+1;
run;
Yes, you are correct. I should have given some more detailed data. Here it is .
data have;
infile cards truncover;
input subject visit $ day v_1 $ d_1 v_2 $ d_2 v_3 $ d_3 v_4 $ d_4 v_5 $ d_5 v_6 $ d_6 v_7 $ d_7 v_8 $ d_8 v_9 $ d_9 v_10 $ d_10 v_11 $ d_11 v_12 $ d_12 v_13 $ d_13;
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 . . .
34567 C1 1 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C1 2 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C10 197 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C10 198 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C10 205 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C2 22 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C3 43 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C3 59 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C4 65 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C5 85 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C6 106 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C7 134 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C8 155 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C9 176 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
34567 C9 184 C1D1 1 C2D1 22 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 . . .
45678 C1 1 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C1 2 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C2 23 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C3 43 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C4 64 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C5 85 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C6 113 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
45678 C7 134 C1D1 1 C2D1 23 C3D1 43 C4D1 64 C5D1 85 C6D1 106 C7D1 134 . . . . . . .
56789 C1 1 C2D1 24 . . . . . . . . . . . . .
56789 C1 3 C2D1 24 . . . . . . . . . . . . .
56789 C1 4 C2D1 24 . . . . . . . . . . . . .
56789 C2 24 C2D1 24 . . . . . . . . . . . . .
56789 C2 25 C2D1 24 . . . . . . . . . . . . .
67890 C1 1 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C1 2 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C10 197 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C11 218 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C12 239 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C13 260 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C2 22 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C3 43 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C4 71 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C5 92 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C5 106 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C5 107 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C6 113 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C7 134 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C8 155 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
67890 C9 176 C1D1 1 C2D1 22 C3D1 43 C4D1 71 C5D1 92 C6D1 113 C7D1 134 C8D1 155 C9D1 176 C10D1 197 C11D1 218 C12D1 239 C13D1 260 260
;
run;
Hi @gpv2000 I modified the previous and did you test with that? here it is again .
data want;
set have;
array t(*) d_:;
cycle_day=day- t(input(substr(visit,2),8.))+1;
run;
Thank you . Your latest suggestion worked. Would you mind explaining your logic. I am bad with arrays.
1. The array merely groups your d_ series of variables
2. substr extracts the number from the visit number
3. the extracted number is used as the index to identify the corresponding element in the array d_ series
4. the value of the corresponding element is extracted to plug in your formula
5. Important note : In SAS, array is not a data structure rather just a grouping of elements unlike other programming languages and can be refereed using numeric indexes
Thanks for the detailed explanation.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.