turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- rounding counts in proc tabulate, and ods excel up...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2018 09:46 AM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sasecn

03-20-2018 10:38 AM

@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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

03-20-2018 10:56 AM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sasecn

03-20-2018 11:14 AM

@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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

03-20-2018 01:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sasecn

03-20-2018 03:18 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

03-20-2018 04:21 PM

Thanks a lot!