SQL with initial space

[arodriguez]

Hi everyone,

I was wondering if there is any way to conserve the left blancs with SQL. See the following example

data tmp;

cat="First level  ";

output;

cat=" Second level";

output;

cat="  Third level";

output;

;

run;

proc sql noprint;

  select distinct cat into :var_levels separated by "#"

  from tmp;

quit;

%put &var_levels;

I obtain this output

Third level#Second level#First level

and I like to have

Third level# Second level#  First level.

[EricHoogenboom · Accepted solution]

From the doc 🙂

notrim

separated by "#" notrim

Hth,

Eric

[arodriguez]

Thanks, like in almost everywhere the text is not trim I have not think about a possible option to do it

[RW9]

Hi,

I think SQL will trim off the leading blanks by default, I don't know of a way to switch it off.  However you can easily fool it:

data tmp;

cat="First level  ";

output;

cat="_Second level";

output;

cat="__Third level";

output;

;

run;

proc sql noprint;

  select distinct cat into :var_levels separated by "#"

  from tmp;

quit;

%put %sysfunc(tranwrd(&var_levels,_, ));

Depends on what you want it for really.  If I am doing rtf output then I would put the rtf code in the variable.  Alternatively if you are creating a string from the 3 levels, then a retain, if last then output, should work.  Or you could tranpose them etc.