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;
run;

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!

 

tags: macro variable, sas programming, sas/graph

One Comment

  1. Sunil Gupta
    Posted February 4, 2013 at 10:06 am | Permalink

    Thanks for the tip to use the 'separated by' to remove blanks!

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>