☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Condition statement in MACRO

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jade | Level 19

## Re: Condition statement in MACRO

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;``````
6 REPLIES 6
Jade | Level 19

## Re: Condition statement in MACRO

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.

Jade | Level 19

## Re: Condition statement in MACRO

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;``````
Fluorite | Level 6

## Re: Condition statement in MACRO

Hi Andrees,
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?
Jade | Level 19

## Re: Condition statement in MACRO

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);``

Jade | Level 19

## Re: Condition statement in MACRO

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;``````
Fluorite | Level 6

## Re: Condition statement in MACRO

Thank you so much for your help.
It is working
Discussion stats
• 6 replies
• 508 views
• 1 like
• 2 in conversation