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