Hi Experts,
I am facing challenges with one of my activities. Please find below scenario and kindly provide the solution.
Input Table:
Patient | Plan_TRT | ACT_TRT_VIS2 | ACT_TRT_VIS4 | ACT_TRT_VIS5 | ACT_TRT_VIS6 | ACT_TRT_VIS21 | ACT_TRT_VIS51 | ACT_TRT_VIS41 |
101 | TRTA 10MG | TRTA 10MG | ||||||
102 | TRTA 10MG | TRTA 10MG | TRTA 5MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | ||
103 | TRTB 5MG | TRTB 10MG | TRTA 5MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | |
104 | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO |
105 | TRTB 5MG | TRTB 5MG | TRTB 5MG | |||||
106 | TRTA 10MG | TRTA 5MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG |
Output Table Required: (FINAL_ACTUAL_TRT is required)
Patient | Plan_TRT | ACT_TRT_VIS2 | ACT_TRT_VIS4 | ACT_TRT_VIS5 | ACT_TRT_VIS6 | ACT_TRT_VIS21 | ACT_TRT_VIS51 | ACT_TRT_VIS41 | FINAL_ACTUAL_TRT |
101 | TRTA 10MG | TRTA 10MG | TRTA 10MG | ||||||
102 | TRTA 10MG | TRTA 10MG | TRTA 5MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | TRTB 5MG | ||
103 | TRTB 5MG | TRTB 10MG | TRTA 5MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | TRTB 10MG | TRTB 5MG | |
104 | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO | PLACEBO |
105 | TRTB 5MG | TRTB 5MG | TRTB 5MG | TRTB 5MG | |||||
106 | TRTA 10MG | TRTA 5MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG | TRTA 10MG |
Conditions:
I googled and trying to approach with below code. 1. Finding if all variables are have same values or not. If all records are having same values then planning to assign FINAL_ACTUAL_TRT = Plan_TRT .
data want;
set test3;
array c{*} $ VIS2 VIS4 VIS5 VIS6 VIS21 VIS51 VIS41;
if cmiss(of c{*})=0 then result='same';
else result='diff';
do I=2 to dim(c) while (result='same');
if c{i}^=c{i-1} then result='diff';
end;
run;
Requesting you to provide the better solution for me. Thanks you very much in advance for your support.
Kind Regards,
RJ
Provide generic rules. Examples provided for specific patients do not provide a general rule that I could apply to other patients that do not have exactly the same values for the given variables.
You might find the WHICHC function helpful. It will return the position of number of the first value if found in a list of values/variables.
whichc (Plan_trt, of c(*))
would return the position of the value of Plan_trt if found in the array c or 0 if not found. So a 3 would mean that what ever value of Plan_trt may have was found in VIS5 given your array C definition.
Are the variables shown the only ones? I ask because the naming convention with VIS2 VIS4 VIS5 VIS6 VIS21 VIS51 VIS41 might indicate many missing variables such as VIS10 or VIS22 through VIS40.
If they are the only variables then the "all" case may be easiest as
if VIS2= VIS4= VIS5= VIS6= VIS21= VIS51= VIS41=Plan_TRT then Final_Actual_trt= Plan_trt ;
But I am not sure that when you say "all" that I am interpreting that as you might intend.
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.
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 25. 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.