Dump wide table to HTML report (SAS)

This SAS macro dumps a data set with many variables to an HTML report in a single line of SAS code. It’s useful on wide data sets (or in database speak, a wide table with many columns), with long character values, or where the customer needs to focus on individual observations. It’s similar to PROC PRINT, but transposed: each variable (column) in the dataset (table) has its own row. That is, columns become rows. Furthermore, there is a page break for printing (yes, in HTML) after each observation in the data set.

You don’t need to know the SAS macro language to use this macro, the macro demonstrates some interesting techniques for those learning SAS macro programming.

I created this macro during development of a program where I needed a easy, convenient way to show the customer the data set, which contains many rows and many values have long character strings. If I used PROC PRINT, it would have been too wide to read. Also, the customer was used to seeing this data in a Microsoft Office Word mail merge which has about the same format as the macro, and Word 2007 opens the HTML file (though it loses the page breaks).

This is the macro:

%macro wide_report(sm_dataset=, sm_pathname=, sm_title=SAS Output);
/* get names into data set */
proc contents data=&sm_dataset out=&sm_dataset._vars noprint;

/* get names into macro */
proc sql noprint;
	select name
	into :name1 - :name999
	from &sm_dataset._vars
	order by varnum

/* open a file reference */
filename fref "&sm_pathname";

/* create an HTML file */
data _null_;
	file fref;
	set &sm_dataset;
	if _n_ eq 1 then do;
		put '<html>';
		put '<head>';
		put "<title>&sm_title</title>";
		put '</head>';
		put '<body>';
	put '<table>';
	/* loop over the variables */
	%let i = 1;
	%let name = name&i;
	%do i=1 %to &SQLOBS;
		%let name = name&i;
		put "<tr><th>&&name&I.</th><td>";
		put &&name&i.;
		put '</td></tr>';
	put '</table>';
	/* Draw a horizontal rule with a page break.
	 * The page break only shows when printing. */
	put '<hr style="page-break-after:always">';

/* close file */
data _null_;
	file fref mod;
	put "<!-- Generated %sysfunc(datetime(), dateampm.)  -->";
	put '</body>';
	put '</html>';

filename fref;

/* remove temporary table */
proc sql;
	drop table &sm_dataset._vars;

%mend wide_report;

Here’s an example of how to call the macro:

/* Limit to 50 observations to make the HTML short */
data zip;
	set sashelp.zipcode(obs=50);

/* Call the macro */
%wide_report(sm_dataset=zip, sm_pathname=zip.html, sm_title=US Zip Codes);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s