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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.