BookmarkSubscribeRSS Feed
sasecn
Quartz | Level 8

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!

6 REPLIES 6
ballardw
Super User

@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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1093 views
  • 1 like
  • 2 in conversation