Assigning cycle day

[gpv2000]

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

[PGStats]

Does v_1 ever contain something else than "C1D1", does v_2 ever contain something else than "C2D1", etc?

[gpv2000]

That dataset is a result of transpose procedure and the values has such will not change. V1 will either be missing or c1d1.

[gpv2000]

Sorry I take my earlier response back. In my data i see once this does not happen. I am checking if that is a data issue.

[novinosrin]

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;

[gpv2000]

It works except for the cases when cycle is greater than 9.

[novinosrin]

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;

[gpv2000]

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;

[novinosrin · Accepted solution]

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;

[gpv2000]

Thank you . Your latest suggestion worked. Would you mind explaining your logic. I am bad with arrays.

[novinosrin]

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 

[gpv2000]

Thanks for the detailed explanation.