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

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_1WLOS_2WLOS_3WLOS_4WNP_1WNP_2WNP_3WNP_4WCR_1WCR_2WCR_3WCR_4WRatio_1WRatio_2WRatio_3WRatio_4W
05303.42.932.986.575.5.81.84.24.141.1
100403.42.9.2.986.575.5.81.84.24.141.1
06203.42.932.986.575.576.381.84.24.141.1
27003.42.932.986.575.5.81.84.24.141.1

 

 

The outcome I wish is :

NP_wtdCR_wtd
3.0875.5
3.486.5
2.9275.7
3.0177.96

 

Thank you

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

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
Jade | Level 19

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
Fluorite | Level 6
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
Jade | Level 19

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
Jade | Level 19

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
Fluorite | Level 6
Thank you so much for your help.
It is working

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 508 views
  • 1 like
  • 2 in conversation