I'm creating a macro variable but when use the same macro variable in my Proc Report this macro is generating a space in front of the value
Select COUNT(DISTINCT USUBJID) into: N1 from DMDD where ARMN=1; 
How do I rectify it in the same code??
3
votes
4 Answers
7
votes
This is actually 'working as designed' for PROC SQL SELECT INTO. While all of the other answers are, in some ways, correct, this is a special case as opposed to normal macro variables, such as
%let var= 5 ;
%put [&var];
where that will return just
[5]
while this is not doing that. That is a behavior of PROC SQL SELECT INTO, and is intentional.
These two statements:
proc sql;
select name into :name from sashelp.class where name='Alfred';
select name into :shortname separated by ' ' from sashelp.class where name='Alfred';
quit;
%put `&name` `&shortname`;
are non-identical. separated by ' ' (or any other separated by) will always trim automatically unless notrim is included, and if you have 9.3 or newer, you have a new option, trimmed, which you can use if you intend to select a single variable. I think this behavior was introduced in 9.2 (the non-trimming of select into without a separated by, by default).
If you are only selecting a single value, adding separated by ' ' will have no impact on your result other than to cause the trimming to occur.
5
votes
This is because any macro variable is stored as a character. If the source data is numeric then SAS uses the best12. format to convert to character, therefore the result is padded with leading blanks. I get around this by using the CATS function which strips out leading and trailing blanks. You can't use the LEFT or STRIP functions as these only work against character variables.
Select cats(COUNT(DISTINCT USUBJID)) into: N1 from DMDD where ARMN=1;
