Hello all,
I have below data
SUB | Arm | Screen | Week1 | Week2 | Week3 | Week4 | Week5 | Week6 | Week7 | Week8 | Week9 | Week10 | TIME TO ACHIVE TARGET 10 |
1 | Test | 8 | 8.2 | 8.4 | 8.6 | 8.8 | 9 | 9.2 | 9.4 | 9.6 | 9.8 | 10.1 | 10 |
2 | Test | 8.7 | 8.9 | 9.1 | 9.3 | 9.5 | 9.7 | 9.9 | 10.1 | 10.3 | 10.5 | 10.7 | 7 |
3 | Ref | 9 | 9.2 | 9.4 | 9.6 | 9.8 | 10 | 10.2 | 10.4 | 10.6 | 10.8 | 11 | 5 |
4 | Test | 9.5 | 9.7 | 9.9 | 10.1 | 10.3 | 10.5 | 10.7 | 10.9 | 11.1 | 11.3 | 11.5 | 3 |
5 | Ref | 9.8 | 10 | 10.2 | 10.4 | 10.6 | 10.8 | 11 | 11.2 | 11.4 | 11.6 | 11.8 | 1 |
6 | Ref | 10.3 | 10.5 | 10.7 | 10.9 | 11.1 | 11.3 | 11.5 | 11.7 | 11.9 | 12.1 | 12.3 | - |
7 | Test | 8 | 8.2 | 8.4 | 8.6 | 8.8 | 9 | 9.2 | 9.4 | 9.6 | 9.8 | 10 | 10 |
8 | Ref | 9.2 | 9.4 | 9.6 | 9.8 | 10 | 10.2 | 10.4 | 10.6 | 10.8 | 11 | 11.2 | 4 |
9 | Ref | 8.6 | 8.8 | 9 | 9.2 | 9.4 | 9.6 | 9.8 | 10 | 10.2 | 10.4 | 10.6 | 7 |
10 | Test | 8.3 | 8.5 | 8.7 | 8.9 | 9.1 | 9.3 | 9.5 | 9.7 | 9.9 | 10.1 | 10.3 | 9 |
i want to check that at which week subject has reached score of 10, in last column answer are there i want that column to be generated by coding if score is greater then 10 at screen we will drop that subject.
Thanks in advance.
@jay_bhavsar wrote:
Hi friend,
may be i failed to explain the query i run the the code but its not giving answer as i accepted. let me explain you the situation.
SUB 1 reached to score of more then 10 at week10 so "Time to achieve target 10" has value 10.
Likewise SUB 1 reached to score of more then 10 at week7 so "Time to achieve target 10" has value 7.
and for sub 3 its week5, for sub 4 its week3, for sub 5 its week1, for sub 7 its week10, for 8 week4 and so on.
and what if all Column name is not in sequence (Ex: Jan Mar May Sep Dec) then how can we get values of time to achieve target 10.
There are no variables named "Jan", "Mar", etc in your data. Please post the data you have in usable form and with the variable names you have.
Please explain the role of the variables "Arm" and "Screen", are they necessary to calculate "TimeToAchieveTarget10"?
Untested:
data want;
set have;
length Target10 8;
array values Week1-Week10;
do i = 1 to dim(values);
if values[i] >= 10 then do;
Target10 = input(compress(vname(values[i]),,'kd'), 2.);
leave;
end;
end;
run;
/* UNTESTED CODE */
data want;
set have;
if screen>10 then delete;
array w week1-week10;
array ww _temporary_;
do i = 1 to dim(w);
ww(i)=(w(i)>=10);
end;
time_to_achieve_target_10 = whichn(1,of ww[*]);
drop i;
run;
If you want tested code, please provide data as a SAS data set (instructions)
@jay_bhavsar wrote:
Hi friend,
may be i failed to explain the query i run the the code but its not giving answer as i accepted. let me explain you the situation.
SUB 1 reached to score of more then 10 at week10 so "Time to achieve target 10" has value 10.
Likewise SUB 1 reached to score of more then 10 at week7 so "Time to achieve target 10" has value 7.
and for sub 3 its week5, for sub 4 its week3, for sub 5 its week1, for sub 7 its week10, for 8 week4 and so on.
and what if all Column name is not in sequence (Ex: Jan Mar May Sep Dec) then how can we get values of time to achieve target 10.
There are no variables named "Jan", "Mar", etc in your data. Please post the data you have in usable form and with the variable names you have.
Please explain the role of the variables "Arm" and "Screen", are they necessary to calculate "TimeToAchieveTarget10"?
Untested:
data want;
set have;
length Target10 8;
array values Week1-Week10;
do i = 1 to dim(values);
if values[i] >= 10 then do;
Target10 = input(compress(vname(values[i]),,'kd'), 2.);
leave;
end;
end;
run;
Thank you Andreas,
The code is giving desired results. One more thing to see what is some weeks in between are not there (file attached).
variables "Arm" and "Screen" is just for carry no need to check condition in that.
@jay_bhavsar wrote:
Thank you Andreas,
The code is giving desired results. One more thing to see what is some weeks in between are not there (file attached).
variables "Arm" and "Screen" is just for carry no need to check condition in that.
Sorry, but i won't open any office-file, please post the data in usable form.
Earlier, I gave a link to instructions to provide your data as SAS data step code. That's the best form to use.
OK i am attaching file.
Many of us will not download files. The SAS code should be included in your reply, as text by pasting it into the window that appears when you click on the "running man" icon.
This data is dramatically different than what you posted originally, and there are many places where the value is 1 whereas before it was 10, which of course changes the results. Are these typographical errors?
Could you please correct all of your data set (or inform me that it already is correct)? Could you please post the code in a code box as I requested?
The posted data is same as before. the only difference is i removed some column to see how code can be apply when the sequence of Week1-Week10 is not there.
As for all available columns the code provided previously is working perfectly.
but i just curious to know what if we have columns which are not in sequence.
The posted data is same as before.
No it is not.
There were 10s in the original data set, and the value of 1 appears now. Are these typographical errors? If so, please correct them.
Please explain why Time_To_Achieve_Target is 10 in the first line of the data you have posted. The maximum value is 9.8 in Week9.
The code i posted will need at one update if some week-variables don't exist. Changing the array-statement to
array values Week:;
should be sufficient, assuming that there are no variables in the dataset whose names start with "week" but must not considered in setting Time_To_Achieve_Target. And the variables must be properly sorted, if Week5 appears before Week4 the loop won't give the expected results.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: