DATA Step, Macro, Functions and more

SQL Macro value has "space" in front

Accepted Solution Solved
Reply
Super Contributor
Posts: 506
Accepted Solution

SQL Macro value has "space" in front

Hi Everyone,

I use SQL to save macro value.

Some how when I use it in code, the value has space in front which return error.

 

proc sql noprint;  select RR into: RR from w; quit;
%put "macro value:" &rr

...
keep type_RR&RR._sl

 

when &RR=3, the above code return these value. there is space in front.

In SAS log, that space appear to be a "tab" (big gap).


"macro value:" 3
Type_RR 3_sl

Can you please help me with that?

 

Thank you,

 

HHCFX


Accepted Solutions
Solution
‎04-06-2018 12:38 PM
Super User
Posts: 4,025

Re: SQL Macro value has "space" in front

[ Edited ]

Yes, like this:

proc sql noprint;
   select age into :age trimmed
   from sashelp.class
   where name = 'Mary'
   ;
quit;

%put age = &age;

Or you can use SQL functions to get the same result:

proc sql noprint;
   select strip(put(age, 8.)) into :age 
   from sashelp.class
   where name = 'Mary'
   ;
quit;

 

View solution in original post


All Replies
Super User
Posts: 24,010

Re: SQL Macro value has "space" in front

TRIMMED option

 

Super Contributor
Posts: 506

Re: SQL Macro value has "space" in front

My trick is:

after the SQL to get macro variable RR, I put the code

 

%let RR=&RR;

 

so the problem is solved.

But so curious why that space come in the first place.

Super User
Posts: 24,010

Re: SQL Macro value has "space" in front

That's an extra step and a really old way to deal with it.


@hhchenfx wrote:

My trick is:

after the SQL to get macro variable RR, I put the code

 

%let RR=&RR;

 

so the problem is solved.

But so curious why that space come in the first place.


 

It's because macro variables are characters and you didn't specify a length so it assumed one and added spaces for it. 

Respected Advisor
Posts: 3,867

Re: SQL Macro value has "space" in front

numeric to character conversion. right justified in a field 12 bytes long
Super Contributor
Posts: 506

Re: SQL Macro value has "space" in front

Posted in reply to data_null__

So we can do Trim inside the SQL?

Solution
‎04-06-2018 12:38 PM
Super User
Posts: 4,025

Re: SQL Macro value has "space" in front

[ Edited ]

Yes, like this:

proc sql noprint;
   select age into :age trimmed
   from sashelp.class
   where name = 'Mary'
   ;
quit;

%put age = &age;

Or you can use SQL functions to get the same result:

proc sql noprint;
   select strip(put(age, 8.)) into :age 
   from sashelp.class
   where name = 'Mary'
   ;
quit;

 

Super User
Posts: 10,850

Re: SQL Macro value has "space" in front

proc sql noprint; 
 select RR into: RR   separated by ' '
 from w;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 197 views
  • 2 likes
  • 5 in conversation