SAS Proc SQL Trim not working?

Question

I have a problem that seems pretty simple (probably is...) but I can't get it to work.

The variable 'name' in the dataset 'list' has a length of 20. I wish to conditionally select values into a macro variable, but often the desired value is less than the assigned length. This leaves trailing blanks at the end, which I cannot have as they disrupt future calls of the macro variable.

I've tried trim, compress, btrim, left(trim, and other solutions but nothing seems to give me what I want (which is 'Joe' with no blanks). This seems like it should be easier than it is..... Help.

data list;
    length id 8 name $20;
    input id name $;
cards;
1 reallylongname
2 Joe
;
run;

proc sql;
    select trim(name) into :nameselected
    from list
    where id=2;
run;

%put ....&nameselected....;

score 14 · Accepted Answer · answered Jan 09 '15 at 16:14

14

Actually, there is an option, TRIMMED, to do what you want.

proc sql noprint;
    select name into :nameselected TRIMMED
    from list
    where id=2;
quit;

Also, end PROC SQL with QUIT;, not RUN;.

answered Jan 09 '15 at 16:14

DomPazz

12,415
17
23

@Jeff had an answer that worked, but this one is cleaner. Thanks. – pyll Jan 09 '15 at 17:23
1

I've never seen this before. Cool. +1. – Alex A. Feb 04 '15 at 22:12

score 6 · Answer 2 · answered Jan 09 '15 at 15:41

6

It works if you specify a separator:

proc sql;
    select trim(name) into :nameselected separated by ''
    from list
    where id=2;
run;

answered Jan 09 '15 at 15:41

Jeff

1,787
9
14

Yep, that did it. Thanks, Jeff. Seems like what I have should work, though..... – pyll Jan 09 '15 at 15:47
1

Interestingly, the separator doesn't even have to blank. – Jeff Jan 09 '15 at 16:00
2

@pyll For some reason, they chose to make TRIMMED the default with separated-by and NOTRIM the default otherwise. I have no idea why. – Joe Jan 09 '15 at 21:17

SAS Proc SQL Trim not working?

2 Answers2