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 a quarter century, and his specialty is customizing graphs and maps - adding those little extra touches that help them answer your questions at a glance. His educational background is in Computer Science, and he holds a BS, MS, and PhD from NC State University.

Related Posts


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

Back to Top