Assigning cycle day

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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 : 

 

subjectvisitdayv_1d_1v_2d_2v_3d_3v_4 d_4v_nd_ncycle_day
12345C11C1D11C2D122C3D143                .  1
12345C12C1D11C2D122C3D143                .  2
12345C110C1D11C2D122C3D143                .  10
12345C222C1D11C2D122C3D143                .  1
12345C343C1D11C2D122C3D143                .  1
23456C11C1D11C2D122C3D151C4D172  1
23456C13C1D11C2D122C3D151C4D172  3
23456C222C1D11C2D122C3D151C4D172  1
23456C351C1D11C2D122C3D151C4D172  1
23456C472C1D11C2D122C3D151C4D172  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;

View solution in original post


All Replies
Esteemed Advisor
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

Posted in reply to novinosrin

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

Posted in reply to novinosrin

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

Posted in reply to novinosrin

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

Posted in reply to novinosrin

Thanks for the detailed explanation.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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