Shortcut for variable names

[zishaq]

Hi,

I'm looking for a shorthand way to write the following:

pay_01= round(coalesce(gross_01,0) + coalesce(pension_01,0) + coalesce(nic_01,0) , 0.01) ;
pay_02= round(coalesce(gross_02,0) + coalesce(pension_02,0) + coalesce(nic_02,0) , 0.01) ;
pay_03= round(coalesce(gross_03,0) + coalesce(pension_03,0) + coalesce(nic_03,0) , 0.01) ;
pay_04= round(coalesce(gross_04,0) + coalesce(pension_04,0) + coalesce(nic_04,0) , 0.01) ;
pay_05= round(coalesce(gross_05,0) + coalesce(pension_05,0) + coalesce(nic_05,0) , 0.01) ;
pay_06= round(coalesce(gross_06,0) + coalesce(pension_06,0) + coalesce(nic_06,0) , 0.01) ;

Can this be shortened to one line of code instead of having to compute for each of the 6 months?

Thanks

[biopharma]

You are looking for a macro then. 

%macro repeat (max=) ;
   %do i = 1 %to &max ;
      pay_0&i= round(coalesce(gross_0&i,0) + coalesce(pension_0&i,0) + coalesce(nic_0&i,0) , 0.01) ;
   %end ;
%mend  repeat ;
%repeat (max=6) ;

[smantha]

did you try using the sum function

[smantha]

%macro repeat (max=) ;
   %do i = 1 %to &max ;
      pay_0&i= round(sum(gross_0&i,pension_0&i, nic_0&i,0) , 0.01) ;
   %end ;
%mend  repeat ;
%repeat (max=6) ;

[ed_sas_member]

Hi @zishaq 

you can use a %do Loop in a macro program in order to be able tu replace the number by a macro variable as follows:

%macro test ();
...
%do i=1 %to 6;
pay_0&i.= round(coalesce(gross_0&i.,0) + coalesce(pension_0&i.,0) + coalesce(nic_0&i.,0) , 0.01) ;
%end;
...
%mend;


%test()

[Kurt_Bremser]

Y'all suffer from macro-itis.

[Kurt_Bremser]

array pay {*} pay_01-pay_06;
array gross {*} gross_01-gross_06;
array pension {*} pension_01-pension_06;
array nic {*} nic_01-nic_06;
do i = 1 to 6;
  pay{i} = round(sum(gross{I}, pension{I}, nic{I}),.01);
end;
drop i;

It would, of course, be much easier to use an untransposed dataset. 

[Reeza]

Arrays are the correct approach here, though another option would be to transpose your data and operate on a long format. I suspect in general, the long format is better. 

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/