GetWidthVector Macro - Measure the width of your longest character string

From sasCommunity
Jump to: navigation, search

by Don Gallogly

Below is macro code created by Don Gallogly, Oregon Department of Consumer and Business Services. It can be used to find the width of the longest item (character string or number) in a data field, the results of which can be used in the absolute_column_width option of the ExcelXP tagset.

/*		This macro looks at a dataset and produces a macro variable containing a vector
/*		of the widest items in each variable.  For example, a name variable with 
/*		items Tom, Bob, Donald, Dave, and Dick will have the value 6.  The macro
/* 		variable WidthVector can be passed to the absolute_column_width option of the
/*		ODS ExcelXP tagset like this,
/*		ods tagset.excelxp options(absolute_column_width=&WidthVector);
/*		Parameters:
/*			Data - Required.  The name of your dataset.
/*			varlist - Optional. The items that are going to be printed to the 
/*			spreadsheet in the order they are going to be printed.  If this parameter
/*			is omitted, the macro assumes that you want all the variables in the 
/*			dataset.  The variables in varlist should be separated by a space only.
/*     Owners: Don Gallogly, DCBS - IMD
/*     Created: March 2010
%macro GetWidthVector(data, varlist);
	%global WidthVector;
/* If varlist is omitted, then get the list from proc contents */
	%if %length(&varlist) = 0 %then %do;
		proc contents data=&data noprint out=vardata;
		data _null_;
			format vars $100.;
			retain vars;
			set vardata;
			vars = trim(left(vars)) || ' ' || trim(left(name));
			call symputx('varlist', vars);
	proc delete data=vardata;
/* Count the number of variables in the varlist */
	%let i=1;
	%do %until(%scan(&varlist,&i)=%scan(&varlist,-1));
		%let var&i = %scan(&varlist,&i);
		%let i = %eval(&i+1);
	%let var&i = %scan(&varlist,&i);
	%let numvars=&i;
/*	Scan the data set to find the widest of each of the variables in varlist */
	data _null_;
		%do i = 1 %to &numvars; max&i=0; %end;
		set &data;
		retain %do i = 1 %to &numvars; max&i %end;;
		%do i = 1 %to &numvars;	max&i=max(max&i,length(&&var&i)); %end;
		call symputx('widthvector', %do i = 1 %to %eval(&numvars-1); trim(left(max&i)) || ',' || %end;
	%put &WidthVector;

Presented at a State of Oregon SAS Users Group meeting.
Find more SOSUG presentations, as well as other tips, tricks, and tools created by or recommended by SOSUG members here.