Remove leading blanks when creating macro variables using PROC SQL in SAS

I regularly use PROC SQL to create macro variables in SAS, and I recently noticed a strange phenomenon when resolving a macro variable within double quotation marks in the title of a plot.  Thankfully, I was able to replicate this problem using the SASHELP.BASEBALL data set, which is publicly available.  I was then able to send the code and the strange result to SAS Technical Support for their examination.

proc sql;
     select count(name)
     into   :hitters_100plusHR
     from   sashelp.baseball
     where  CrHome > 100;
quit;


proc sgplot
     data = sashelp.baseball;
     histogram Salary;
     title1 'Distribution of salaries';
     title2 "Restricted to the &hitters_100plusHR hitters with more than 100 career home runs";
run;

 

Here is the resulting plot.  Notice the extra spaces before “72” in the title of the plot.

SAS Technical Support informed me that

  • this problem is commonly known.
  • there is no way of predicting when it will occur
  • for now, the best way to deal with it is to remove the leading blanks using one of several ways.

The Solutions

1) Use the %LET statement to assign the variable to itself.

Before PROC SGPLOT, I can write the following code to remove the leading blanks.

%let hitters_100plusHR = &hitters_100plusHR;

Here is the resulting plot.  Notice that there are no more leading blanks before “72” in the title.

 

2) Use the %CMPRES function to remove the leading blanks within the TITLE2 statement.

title2 "Restricted to the %cmpres(&hitters_100plusHR) with more than 100 career home runs";

 

3) Use the COMPBL function and the %SYSFUNC function to

title2 "Restricted to the %sysfunc(compbl(&hitters_100plusHR)) with more than 100 career home runs";

 

I will allow you to try these solutions on your own to confirm their functionality.

 

I thank Martin Mincey from SAS for his help on this issue.

Advertisements

4 Responses to Remove leading blanks when creating macro variables using PROC SQL in SAS

  1. Andrii Korchak says:

    Option ‘SEPARATED BY’ will solve your problem.

    proc sql;
    select count(name)
    into :hitters_100plusHR separated by ‘ ‘
    from sashelp.baseball
    where CrHome > 100;
    quit;

    It works because ‘SEPARATED BY’ enables TRIM option.

  2. Bala says:

    Hi Eric
    Not sure if you tried TRIMMED option in proc sql to handle leading and trailing blanks.

    http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n1tupenuhmu1j0n19d3curl9igt4.htm

Your thoughtful comments are much appreciated!

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: