rounding counts in proc tabulate, and ods excel updating sheet name

[sasecn]

I am using the following data sets for testing:

%macro getdata;
%do yr=2000 %to 2004;
data cars&yr.;
  set sashelp.cars (keep=type origin);
  type&yr.=type;
  origin&yr.=origin;
run;
%end;
%mend getdata;
%getdata;

My goal is to use proc tabulate to get cross-tab for each year by using:

ods excel file="H:\test.xlsx";
%macro tables;
%do yr=2000 %to 2004;
proc tabulate data=cars&yr.;
class type&yr. origin&yr.;
table type&yr.='', origin&yr.=''*(n='n') (all='row totals')/misstext='0';
run;
%end;
%mend tables;
%tables;
ods excel close;

 My questions are:

1. how to round all counts based on 5?

2. how to change the sheet name inside the loop? I tried to put ods statements inside the loop, but it will overwrite the sheets and only produce the last one. 

Thanks for any help!

[ballardw]

---

@sasecn wrote:

I am using the following data sets for testing:

My goal is to use proc tabulate to get cross-tab for each year by using:

 My questions are:

1. how to round all counts based on 5?

2. how to change the sheet name inside the loop? I tried to put ods statements inside the loop, but it will overwrite the sheets and only produce the last one. 

Thanks for any help!

---

You may need to consider in some detail exactly how you would round something to 5. Suppose you have two categories each with an actual n of 3. Do want to round that to 0 or 5? And if you have an "All" then since the actual total n would be 6 do you want to display 5 or 10? Plus almost any other statistic other than n may look odd if shown with that rounded n.

If the only thing you are doing is displaying n values you might consider using one of the other summary procedures such as Proc Means or summary to get the counts and then round in a data step.

For your sheet names you would need to provide an ods excel options (sheet_name="something")

So

%do yr=2000 %to 2004;

ods excel options (sheet_name="&yr");

Depending on what your actual output is you may also need to set the sheet_interval option if your output consists of more than one table that you want on a sheet. The default is sheet_interval='TABLE' which attempts to place each table onto a separate sheet. Also you might want to look at the sheet_label option as well.

[sasecn]

Thanks for the solution to update excel sheet name.

I would like to round to the nearest 5. e.g. 2 > 0, 3 > 5; 7 > 5; 8 > 10 ..... My actual data will have much larger numbers. The variables are categorical vars. If you think other procedures may be easier to use, can you give me some hints? 

Thanks a lot!

[ballardw]

---

@sasecn wrote:

Thanks for the solution to update excel sheet name.

I would like to round to the nearest 5. e.g. 2 > 0, 3 > 5; 7 > 5; 8 > 10 ..... My actual data will have much larger numbers. The variables are categorical vars. If you think other procedures may be easier to use, can you give me some hints? 

Thanks a lot!

---

The issue is with creating inconsistent data using a proc. Here is another example to contemplate. You have 3 categorical variables each with a count of 2. So they would display 0 for each. But the total would be 6 and display as 5. So you have 0+0+0=5 in the body of your table. The larger the number of categories the higher potential displayed error in the sum could be.

You could build a custom format that calls a custom function built with Proc FCMP that would do the rounding and tell the n statistic to use that format with: n=' '*f=custround. for example. But you are very likely to generate data inconsistencies in your data.

You would be much better off to summarize and round the basic counts to the multiple of 5 prior to a display with proc tabulate. Then your "all rows" total would at least match internally.

[sasecn]

Thanks for the thoughts. I am not familiar with proc fcmp. I will have a look and give it a try.

[ballardw]

Here's an example summarizing, rounding then using tabulate to display the result. Note that a var count is added that you sum.

proc  summary data=sashelp.cars nway;
   class type origin;
   var cylinders;
   output out=carsum (drop=_type_ _freq_) n=count;
run;

data temp;
   set carsum;
   count= round(count,5);
run;

proc tabulate data=temp;
   class type origin;
   var count;
   table type,
        ( origin all='Row total')*count*sum=''*f=best6. 
        /misstext='0';
run;

[sasecn]

Thanks a lot!