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
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) ;
did you try using the sum function
%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) ;
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()
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.
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/
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.