As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Proc Tabulate: Making the result table easier to copy to Excel

From sasCommunity
Jump to: navigation, search

The purpose of the article is to change the default characteristics of HTML result table of the Tabulate procedure into the more attractive.

If you run some basic Summary Tables in SAS Enterprise Guide you will see the result table looking like that one:

D.gif

As you can see, it is quite tidy and understandable.

However if you will copy that easy table to the MS Excel you will probably see:

E.gif

The table here doesn't look that great now. Not only borders and background, but the structure has changed. To view the changes I showed the borders:

Borders.gif

Take a look at the cell B3 on the picture above. The cells B2 and B3 are merged. When creating some business reports this merging annoys me as it disallow me to create a diagrams or use some formulas. Moreover, if you unmerge the cells, the value of the first class (Apr) will go to the cell B2 (although it should be in B3). That might seems to be not a big deal, but sometimes you have to run a hundreds of tabulate procedures. In this case every automated step will let you to get rid of hours of ineffective work.

There are 2 options: do changes in Excel (i.e. use VBA Macro) or change the proc tabulate output. We go 2nd way.

Lets take a look at the default SAS code of the tabulate procedure:

PROC TABULATE DATA=TABLE1;
	CLASS month1;
	TABLE month1, N;
RUN;

What we have to do is just to change the lable of the variable month1 to the blank. Look:

PROC TABULATE DATA=TABLE1;
	CLASS month1;
	TABLE month1='', N;
RUN;

Now, run the new code and you will see:

Clean.gif

That's better.

As for the style, there are many ways to express your design passion. The easiest one is to run the Style Manager in SAS Enterprise Guide (Tools-> Style Manager...). This tool will help you to choose the design you like from the list of available styles or create your own style (based on the existing one).

Style m.gif

After you're done with the styles, I would recommend you to make some minor changes to your code. For instance, add the title before the table, to fill the empty box above the rows titles and to set the format.

title 'Annual Report';
PROC TABULATE DATA=TABLE1 format=comma.;
	CLASS month1;
	TABLE month1='', N / box='Month:' ;
RUN;

Final result table should be better:

Final.gif

Now, if you copy this table to the MS Excel you will see a symmetric and consistent table. It will be able to appear correctly on diagrams and to be used with formulas as now it will not have merged cells.

That's all about it.

Good luck.

Victor Popovich