topic Re: SQL with initial space in SAS Programming

SQL with initial space

arodriguez — Fri, 07 Aug 2015 13:45:47 GMT

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.

Re: SQL with initial space

EricHoogenboom — Fri, 07 Aug 2015 14:01:42 GMT

From the doc 🙂

notrim

separated by "#" notrim

Hth,

Eric

Re: SQL with initial space

arodriguez — Fri, 07 Aug 2015 14:04:07 GMT

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

Re: SQL with initial space

RW9 — Fri, 07 Aug 2015 14:04:45 GMT

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.