DATA Step, Macro, Functions and more

SQL with initial space

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 144
Accepted Solution

SQL with initial space

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.


Accepted Solutions
Solution
‎08-07-2015 10:01 AM
Occasional Contributor
Posts: 11

Re: SQL with initial space

From the doc :-)

notrim

separated by "#" notrim

Hth,

Eric

View solution in original post


All Replies
Solution
‎08-07-2015 10:01 AM
Occasional Contributor
Posts: 11

Re: SQL with initial space

From the doc :-)

notrim

separated by "#" notrim

Hth,

Eric

Frequent Contributor
Posts: 144

Re: SQL with initial space

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

Super User
Super User
Posts: 7,401

Re: SQL with initial space

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 211 views
  • 4 likes
  • 3 in conversation