As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Difference between revisions of "Tips:Strip Blanks from PROC SQL-Created Macro Variable Values"

From sasCommunity
Jump to: navigation, search
(October 18, 2011)
(formatted code; see Code Like It Matters for my reasoning & here for more on how query clauses could be formatted)
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
A macro variable created in PROC SQL from a numeric variable or function has leading blanks ...
+
A [[macro variable]] created in [[PROC SQL]] from a numeric variable or [[function]] has leading blanks...
 
<source lang="sas">
 
<source lang="sas">
 
proc sql;
 
proc sql;
select count(*) into :howmany
+
    select count(*) into :howmany
from sashelp.class;
+
    from sashelp.class;
 
quit;
 
quit;
 
%put |&howmany|;
 
%put |&howmany|;
Line 9: Line 9:
 
|      19|
 
|      19|
 
</source>
 
</source>
An easy way to get rid of the leading blanks is as follows ...
+
An easy way to get rid of the leading blanks is as follows...
 
<source lang="sas">
 
<source lang="sas">
 
proc sql;
 
proc sql;
select count(*) into :howmany separated by ''
+
    select count(*) into :howmany separated by ''
from sashelp.class;
+
    from sashelp.class;
 
quit;
 
quit;
 
%put |&howmany|;
 
%put |&howmany|;
Line 20: Line 20:
 
</source>
 
</source>
 
Posted with thanks to [http://robslink.com/SAS/Home.htm Robert Allison].
 
Posted with thanks to [http://robslink.com/SAS/Home.htm Robert Allison].
 +
 +
Since the original post, the TRIMMED option has been added to the INTO clause in PROC SQL. It will also strip blanks.
 +
<source lang="sas">
 +
proc sql;
 +
    select count(*) into :howmany trimmed
 +
    from sashelp.class;
 +
quit;
 +
%put |&howmany|;
 +
 +
|19|
 +
</source>
 +
 
<!-- Insert any appropriate category tags. Note that it is important to put the categorys inside the <noinclude> block so the categories are only applied to the tip page.
 
<!-- Insert any appropriate category tags. Note that it is important to put the categorys inside the <noinclude> block so the categories are only applied to the tip page.
 
- You can have as many CATEGORY tag lines as you want.
 
- You can have as many CATEGORY tag lines as you want.
Line 33: Line 45:
 
- if you don't have such links, just delete the line(s)
 
- if you don't have such links, just delete the line(s)
 
-->
 
-->
{{ExternalReadMore|http://www.nesug.org/Proceedings/nesug99/cc/cc107.pdf}}
+
{{ExternalReadMore|http://www.lexjansen.com/nesug/nesug99/cc/cc107.pdf}}
 
<!-- Please do not edit below this line, EXCEPT when promoting a tip -->
 
<!-- Please do not edit below this line, EXCEPT when promoting a tip -->
 
<div style="float:right">Submitted By [[User:Msz03|Mike Zdeb]]</div>
 
<div style="float:right">Submitted By [[User:Msz03|Mike Zdeb]]</div>
 +
 
<noinclude>
 
<noinclude>
 
[[Category:Tip in Use]]
 
[[Category:Tip in Use]]
 
</noinclude>
 
</noinclude>

Latest revision as of 06:47, 18 October 2017

A macro variable created in PROC SQL from a numeric variable or function has leading blanks...

proc sql;
    select count(*) into :howmany
    from sashelp.class;
quit;
%put |&howmany|;
 
|      19|

An easy way to get rid of the leading blanks is as follows...

proc sql;
    select count(*) into :howmany separated by ''
    from sashelp.class;
quit;
%put |&howmany|;
 
|19|

Posted with thanks to Robert Allison.

Since the original post, the TRIMMED option has been added to the INTO clause in PROC SQL. It will also strip blanks.

proc sql;
    select count(*) into :howmany trimmed
    from sashelp.class;
quit;
%put |&howmany|;
 
|19|

....see also

Submitted By Mike Zdeb