A neat new trick to trim your macro variables in 9.3


SAS macro variables are a great way to store a calculated value, so you can use it later in your code.  They are not just limited to the data step -- you can also use macro variables in title statements, axis statements, etc.

By default, the macro variable will be padded with blanks (per the width of the format). Here's a simple example. Notice how the 'avg' (62.3) has extra spaces padded on the left in the title and in the label for the reference line:

goptions xpixels=600 ypixels=500 gunit=pct htitle=5 htext=3;

proc sql;
select avg(height) format=comma12.1 into :avg from sashelp.class;
quit; run;

axis1 label=('Inches') reflabel=(c=red "&avg");
axis2 label=none offset=(3,6);

pattern1 v=solid c=pink;
pattern2 v=solid c=cx67C8FF;

title "The average class height is &avg inches";
proc gchart data=sashelp.class;
hbar name / type=sum sumvar=height descending
subgroup=sex nostats nolegend coutline=gray
ref=&avg cref=red raxis=axis1 maxis=axis2 noframe;

One way to have sql trim the blanks when creating the macro variable is to use the the 'separated by' option, and tell it the values are separated by blanks.  This was more intended for the scenario where you're outputting multiple values into multiple macro variables... but is also a clever way to trim the blanks when creating a single macro variable.  See how much nicer the title and reference line label look with the blanks trimmed!

proc sql;
select avg(height) format=comma12.1 into :avg separated by ' ' from sashelp.class;
quit; run;


And in SAS 9.3, we've added an even more elegant solution - the 'trimmed' option!

proc sql;
select avg(height) format=comma12.1 into :avg trimmed from sashelp.class;
quit; run;

 You can learn lots of 'tricks' like this, that will make your graphs look better (and make your life simpler) in the SAS/GRAPH training course!



About Author

Robert Allison

The Graph Guy!

Robert has worked at SAS for over 25 years, and is perhaps the foremost expert in creating custom graphs using SAS/GRAPH. His educational background is in Computer Science, and he holds a BS, MS, and PhD from NC State University. He is the author of several conference papers, has won a few graphic competitions, and has written a book (SAS/GRAPH: Beyond the Basics).


  1. Pingback: Обрезаем хвосты с пробелами в макропеременных через PROC SQL | SAS-Education

Leave A Reply

Back to Top