topic Re: SQL Macro value has "space" in front in SAS Programming

SQL Macro value has "space" in front

hhchenfx — Fri, 30 Mar 2018 01:21:00 GMT

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

Re: SQL Macro value has "space" in front

Reeza — Fri, 30 Mar 2018 02:06:37 GMT

TRIMMED option

Re: SQL Macro value has "space" in front

hhchenfx — Fri, 30 Mar 2018 02:08:26 GMT

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.

Re: SQL Macro value has "space" in front

Reeza — Fri, 30 Mar 2018 02:12:42 GMT

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.

Re: SQL Macro value has "space" in front

data_null__ — Fri, 30 Mar 2018 02:15:10 GMT

numeric to character conversion. right justified in a field 12 bytes long

Re: SQL Macro value has "space" in front

hhchenfx — Fri, 30 Mar 2018 02:19:03 GMT

So we can do Trim inside the SQL?

Re: SQL Macro value has "space" in front

SASKiwi — Fri, 30 Mar 2018 03:13:06 GMT

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;

Re: SQL Macro value has "space" in front

Ksharp — Fri, 30 Mar 2018 13:39:47 GMT

proc sql noprint; 
 select RR into: RR   separated by ' '
 from w;