SYMPUTX in SQL—How to Delete the Trailing Blanks

[Junyong]

I have the following data and want to create macro variables using each observation.

data have;
input variable $ abbreviation $;
cards;
apple AP
orange OR
;
run;

The _NULL_ and SYMPUTX combination can pass each observation without leading and trailing blanks as follows.

data _null_;
set have(obs=1);
call symputx("variable",variable);
call symputx("abbreviation",abbreviation);
run;
%put &variable.&abbreviation.;

And the output is

1    %put &variable.&abbreviation.;
appleAP

I tried something similar in SQL with STRIP, but the blanks were there.

proc sql noprint;
select strip(variable),strip(abbreviation)
into :variable,:abbreviation
from have(firstobs=1);
quit;
%put &variable.&abbreviation.;

but

1    %put &variable.&abbreviation.;
apple   AP

It seems SQL respects the length of each variable and locates the values correspondingly. I wonder whether there's something similar to SYMPUTX in SQL as the length of VARIABLE varies observation by observation—for instance, I cannot add LENGTH=5 after STRIP(VARIABLE). Many thanks.

[PaigeMiller · Accepted solution]

proc sql noprint;
select variable,abbreviation
into :variable trimmed,:abbreviation trimmed
from have(firstobs=1);
quit;

[hashman]

@PaigeMiller:

It's a great tip for two reasons:

1. TRIMMED removes both leading and trailing blanks, so using the STRIP function is superfluous. So, the keyword TRIMMED itself is a bit of a misnomer: In all candor, for the sake of semantic consistency, it should be called STRIPPED (or just STRIP would be better).

2. Its behavior, use, indeed the very existence, runs contrary to the SAS documentation here:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1y2jszlvs4hugn14nooftfrxhp3.htm&docse...

because it states, in particular:

(a) That the leading and trailing blanks are removed by default. Yet in reality, they are not.

(b) To preserve them, the NOTRIM option should be used. Yet in reality, an attempt to use NOTRIM results in a syntax error.

(c) TRIMMED is not even mentioned. So, how does one learn about it? Either from the user space (like this one, or user papers) or by introducing a syntax error and looking at the log, which says:

ERROR 22-322: Syntax error, expecting one of the following: ',', -, FROM, SEPARATED, THROUGH, THRU, TRIMMED.

Kind regards

Paul D. 

[Reeza]

 71         proc sql noprint;
 72         select name into :name1 - :name19 notrim from sashelp.class;
 73         select name into :namev1 - :namev19 from sashelp.class;
 74         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5513.46k
       OS Memory           33960.00k
       Timestamp           09/06/2019 07:21:11 PM
       Step Count                        53  Switch Count  0
       Page Faults                       0
       Page Reclaims                     62
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 75         
 76         %put :&name1.:;
 :Alfred  :
 77         %put :&namev1.:;
 :Alfred:

Am I missing something? It seems to work fine for me without any syntax errors.

I'm testing on SAS Studio 9.4M5

[hashman]

@Reeza:

Perhaps you are - and I am, too ... because out of the six queries below, only the last two work:

proc sql noprint ;                                                    
  select name into :name notrim                   from sashelp.class ; *nope;
  select name into :name notrim separated by ","  from sashelp.class ; *nope;
  select name into :name trimmed separated by "," from sashelp.class ; *nope; 
  select name into :name separated by "," trimmed from sashelp.class ; *nope;
  select name into :name trimmed                  from sashelp.class ; *yup;
  select name into :name separated by "," notrim  from sashelp.class ; *yup;
quit ;                                                                

Then there's this part of the confusion: Are the values returned by INTO (a) stripped by default or (b) not stripped? If it is (a), then using TRIMMED is pointless; and if it is (b), then using NOTRIM is pointless.

The above shows that the rules are sort of convoluted, to say at least. I have a nagging suspicion that the default stripping behavior - and the respective validity of TRIMMED and NOTRIM depends on whether or not INTO is used to return a single value or a list. And have you found TRIMMED mentioned anywhere in the documentation? I haven't, but perhaps it's because I assumed that it would be described in the "INTO Clause" chapter of the Macro Language Reference alongside with NOTRIM, while it is ensconced somewhere else. Go figure.

Kind regards

Paul D.   

[PaigeMiller]

It has always been my understand (well, at least for the last few years) that if you have a variable from one observation of a data set (as in the original question above) being placed into a macro variable via the INTO command, that TRIMMED was necessary. If you have a variable from multiple observations, with SEPARATED BY ',' (or any other separator) that by default the values of the variable were TRIMMED, so TRIMMED is not necessary, and only in this case would NOTRIM be needed, if that's what you wanted.

[hashman]

@PaigeMiller:

You may very well be right. But it begs two questions:

1. it should be said so explicitly in the SAS docs, should it not?

2. Why are the trimming/no trimming defaults not uniform across the board?

Kind regards

Paul D. 

[ballardw]

And for even more mystery, using this have data set:

data have;
input variable $ abbreviation $;
cards;
apple AP
;

and a very slight modification of @Reeza's demonstration code:

6181  proc sql noprint;
6182   select variable into :name1 - :name19  from have;
6183   select variable into :namev1  from have;
6184  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


6185  %put :&name1.:;
:apple:
6186  %put :&namev1.:;
:apple   :

So apparently using the : var1- varn applies something differently than extracting just the value.

[PaigeMiller]

---

@hashman wrote:

@PaigeMiller:

It's a great tip for two reasons:

1. TRIMMED removes both leading and trailing blanks, so using the STRIP function is superfluous. So, the keyword TRIMMED itself is a bit of a misnomer: In all candor, for the sake of semantic consistency, it should be called STRIPPED (or just STRIP would be better).

2. Its behavior, use, indeed the very existence, runs contrary to the SAS documentation here:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1y2jszlvs4hugn14nooftfrxhp3.htm&docse...

because it states, in particular:

(a) That the leading and trailing blanks are removed by default. Yet in reality, they are not.

(b) To preserve them, the NOTRIM option should be used. Yet in reality, an attempt to use NOTRIM results in a syntax error.

(c) TRIMMED is not even mentioned. So, how does one learn about it? Either from the user space (like this one, or user papers) or by introducing a syntax error and looking at the log, which says:

ERROR 22-322: Syntax error, expecting one of the following: ',', -, FROM, SEPARATED, THROUGH, THRU, TRIMMED.

Kind regards

Paul D. 

---

Could this be an omission/error in the documentation of PROC SQL? How would we notify SAS that an update to the documentation appears to be necessary?

I think once a long time ago, I opened a ticket through SAS technical support when I found a typographical error in the documentation.

[PaigeMiller]

Apparently, way back in 2013 (SAS version 9.3) the TRIMMED option was introduced! 

https://blogs.sas.com/content/sastraining/2013/01/17/a-neat-new-trick-to-trim-your-macro-variables-i...

[hashman]

@PaigeMiller:

Yup, I've seen this one. But it's not part of the SAS documentation! 

And then, I'd greatly prefer that the documentation put the clear description of TRIMMED and NOTRIM functionality and applicability - alongside with the clear delineation of the cases where leading/trailing blanks are stripped or not by default - in the same location, so that folks wouldn't have to scratch their heads and have this kind of conversation.

Kind regards

Paul D. 

[hashman]

@PaigeMiller:

Methinks this is the way it's done now as well. 

[novinosrin]

Guru @hashman  First off, great info " for the sake of semantic consistency, it should be called STRIPPED (or just STRIP would be better).". Made me laugh

Do you remember what I said about your amazing "sense of humor" more than a year ago on LinkedIn. This one is yet another one. Very witty one too. If anything I know of Guru besides the SAS genie doctorate in physics+knowledge on AN255 is you must possess a Doctorate in humor too. 

PS, I didn't mean to divert the topic or attention of the seriousness of the thread. So my apologies to all however unapologetic to write that funny note. 

[FreelanceReinh]

---

@hashman wrote:

(c) TRIMMED is not even mentioned. So, how does one learn about it? Either from the user space (like this one, or user papers) or by introducing a syntax error and looking at the log, which says:

ERROR 22-322: Syntax error, expecting one of the following: ',', -, FROM, SEPARATED, THROUGH, THRU, TRIMMED.

---

Good point. I came across this omission in the SAS® 9.4 Macro Language Reference in June and then found the documentation in the SAS® 9.4 SQL Procedure User’s Guide (SELECT Statement, INTO Clause😞

macro-variable <TRIMMED>

stores the values that are returned into a single macro variable.

(...)

TRIMMED

trims the leading and trailing blanks from values that are stored in a single macro variable.

[hashman]

@FreelanceReinh:

Thanks for the link! That's what I was looking for - and couldn't locate.

Now this piece of documentation looks as though it is written properly, orderly, and without omissions.

Methinks they should delete the related incomplete (and thus misleading by omission) piece from the Macro Language documentation and merely provide a link to the proc SQL Guide.

Kind regards

Paul D.