Compare Multiple Variables with Another Variable

[RJY9]

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: 

1. It is for Patient 101 - If patient received Plan_TRT treatment at ACT_TRT_VIS2 and other Vists are blank then Fina_Actual_TRT value would be the value of ACT_TRT_VIS2 =  Plan_TRT  that is  “TRTA 10MG”
2. It is for Patient 102 -  If patient received Plan_TRT treatment at ACT_TRT_VIS2 (TRTA 10MG) but at ACT_TRT_VIS4 received a different treatment (TRTA 5MG) again at ACT_TRT_VIS5 switched back to ACT_TRT_VIS2 value and continued the same treatment for all the visits then FINAL_ACTUAL_TRT = Plan_TRT (TRTA 10MG)
3. It is for Patient 103 - If patient received different treatment than Plan_TRT (TRTA 10MG) at ACT_TRT_VIS2 (TRTB 5MG) and changes to Plan_TRT at VIS4 and again moved to  ACT_TRT_VIS2 value and continued to rest of visits with the same value then  FINAL_ACTUAL_TRT =  Plan_TRT  (that is “TRTB 5MG”).
4. It is for Patient 106 – If patient not received plan_TRT at ACT_TRT_VIS2 but later continued with Plan_TRT value then FINAL_ACTUAL_TRT = Plan_TRT (that is “TRTA 10MG”)
5. If patient received Plan_TRT for all then ACTUAL_TRT = Plan_TRT

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;

1. If different values are there for visits then planning to take them to separate table and apply IF conditions. (But, I feel that is a lengthy approach. 

Requesting you to provide the better solution for me. Thanks you very much in advance for your support. 

Kind Regards,

RJ

[ballardw]

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.