Condition statement in MACRO

[Tamertmg]

Hi, I am stuck with the following issue. 

I have the following dataset and I want to do a calculation (numerator/denominator). 

Here is the SAS code I used. 

data test1;
set test;

%macro merge_po(der_var = );

&der_var._wtd =round(sum(&der_var._1W*LOS_1W,&der_var._2W*LOS_2W,&der_var._3W*LOS_3W, &der_var._4W*LOS_4W)/ sum(los_1W, los_2W, los_3W, los_4W),.1);

%mend merge_po;

%merge_po(der_var = NP);
%merge_po(der_var = CR);
run;

My question: 

I want to use a conditional statement for the denominator. for example, if the CR_W3 =. then omit the LOS_3W in the sum stated in the denominator. or if NP_3W=. then omit LOS_3W in the sum indicated in the denominator.

when I run the above code, SAS will include all LOS_1 to LOS_4. 

LOS_1W	LOS_2W	LOS_3W	LOS_4W	NP_1W	NP_2W	NP_3W	NP_4W	CR_1W	CR_2W	CR_3W	CR_4W	Ratio_1W	Ratio_2W	Ratio_3W	Ratio_4W
0	5	3	0	3.4	2.9	3	2.9	86.5	75.5	.	81.8	4.2	4.1	4	1.1
10	0	4	0	3.4	2.9	.	2.9	86.5	75.5	.	81.8	4.2	4.1	4	1.1
0	6	2	0	3.4	2.9	3	2.9	86.5	75.5	76.3	81.8	4.2	4.1	4	1.1
2	7	0	0	3.4	2.9	3	2.9	86.5	75.5	.	81.8	4.2	4.1	4	1.1

The outcome I wish is :

NP_wtd	CR_wtd
3.08	75.5
3.4	86.5
2.92	75.7
3.01	77.96

Thank you

[andreas_lds]

I don't open any attachments, please post data in usable form: a data step using datalines.

As always: starting with code that uses NO macro variables and NO macro statements is recommended.

[andreas_lds]

The code is hardly tested:

data want(keep= np_wtd cr_wtd);
   set have;
   length sum deno np_wtd cr_wtd 8;
   
   array values[2, 4] NP_1W NP_2W NP_3W NP_4W CR_1W CR_2W CR_3W CR_4W;
   array los[4] LOS_1W LOS_2W LOS_3W LOS_4W;
   array results[2] np_wtd cr_wtd;
   
   do j = 1 to 2;
      sum = 0;
      deno = 0;
      
      do i = 1 to 4;
         sum = sum(sum, values[j, i] * los[i]);
         
         if not missing(values[j, i]) then do;
            deno = sum(deno, los[i]);
         end;
      end;
   
      results[j] = round(sum / deno, 0.01);
   end;   
run;

[Tamertmg]

Hi Andrees,
Thank you for your reply.
The code is working for a limited number of variables.
I have around 78 variables that want the same procedure or calculation. Is there any way to combine the code you wrote with the MACRO statement as this will help me to run the code for 78 variables?

[andreas_lds]

So, you have 78 * 4 variables?

You could extend the array definitions:

In

array values[2, 4] NP_1W NP_2W NP_3W NP_4W CR_1W CR_2W CR_3W CR_4W;

the 2 is the number of variable-groups that are processed, for each group increase the first dimension, than add the variables.

Same here, increase the dimension, add a variable to hold the result.

array results[2] np_wtd cr_wtd;

And finally a small update to the outer loop;

do j = 1 to 2;

change it to

do j = 1 to dim(results);

[andreas_lds · Accepted solution]

If you don't like the 2-dim-array, try this:

%macro merge_po(var=);
   array &var. &var._1w &var._2w &var._3w &var._4w;
   sum = 0; 
   deno = 0;
   
   do i = 1 to dim(&var.);
      sum = sum(sum, &var[i] * los[i]);
      
      if not missing(&var[i]) then do;
         deno = sum(deno, los[i]);
      end;
   end;
   
   &var._wtd = round(sum / deno, 0.01);
   
   drop &var._1w &var._2w &var._3w &var._4w;
%mend;

data work.want_macro;
   set have;
   
   array los[4] LOS_1W LOS_2W LOS_3W LOS_4W;
   
   %merge_po(var=np);
   %merge_po(var=cr);
   %merge_po(var=ratio);   
      
   drop i sum deno los:;
run;

[Tamertmg]

Thank you so much for your help.
It is working