## Assigning cycle day

Solved
Occasional Contributor
Posts: 18

# Assigning cycle day

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

Accepted Solutions
Solution
Friday
PROC Star
Posts: 1,770

## Re: Assigning cycle day

[ Edited ]

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;``````

All Replies
Posts: 5,521

## Re: Assigning cycle day

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

PG
Occasional Contributor
Posts: 18

## Re: Assigning cycle day

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

Occasional Contributor
Posts: 18

## Re: Assigning cycle day

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.
PROC Star
Posts: 1,770

## Re: Assigning cycle day

``````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;``````
Occasional Contributor
Posts: 18

## Re: Assigning cycle day

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

PROC Star
Posts: 1,770

## Re: Assigning cycle day

[ Edited ]

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;``````
Occasional Contributor
Posts: 18

## Re: Assigning cycle day

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;``````

Solution
Friday
PROC Star
Posts: 1,770

## Re: Assigning cycle day

[ Edited ]

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;``````
Occasional Contributor
Posts: 18

## Re: Assigning cycle day

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

PROC Star
Posts: 1,770

## Re: Assigning cycle day

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

Occasional Contributor
Posts: 18

## Re: Assigning cycle day

Thanks for the detailed explanation.

☑ This topic is solved.

Discussion stats
• 11 replies
• 130 views
• 2 likes
• 3 in conversation