BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gpv2000
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

11 REPLIES 11
PGStats
Opal | Level 21

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

PG
gpv2000
Calcite | Level 5

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

gpv2000
Calcite | Level 5
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
Tourmaline | Level 20
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
Calcite | Level 5

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

novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

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
Tourmaline | Level 20

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
Calcite | Level 5

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

novinosrin
Tourmaline | Level 20

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
Calcite | Level 5

Thanks for the detailed explanation.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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