creating macro variables via proc sql-remove spaces

[Ronein]

Hello

Please see a code to create a macro variable.

How can we remove spaces in the macro variable   &No_words.?

data tbl1;
 set sashelp.cars;
numWords = countw(model, '');  
Run;



Proc SQL noprint;
select  max(numWords)  into   :No_words
 from tbl1;  
quit;
%put &No_words.;

 I know to do it in proc step but my question is how to do it in proc sql

data tbl1;
 set sashelp.cars  end=eof;
       retain numWords 0;           
       numWords = max(numWords , countw(model, ''));    
       /* for each line of text, how many words */
       if eof then call symputX('No_words',numWords);
Run;
%put &No_words.;

[Kurt_Bremser]

Simply add

%let No_words=&No_words;

%let removes leading and trailing blanks.

[Oligolas · Accepted solution]

Hi,

one solution would be to use the key word 'trimmed'

DATA tbl1;
   set sashelp.cars;
   numWords = countw(model, '');  
RUN;

PROC SQL noprint;
   SELECT  max(numWords) INTO :No_words TRIMMED
   FROM tbl1;  
QUIT;
%put &No_words.;

[ajs_rdg]

Thanks, that's useful.

Documentation here:

INTO Clause
Assigns values produced by PROC SQL to macro variables.
https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1y2jszlvs4hugn14nooftfrxhp3.htm&docse...
Leading and trailing blanks are not trimmed from values before they are stored in macro variables

not as informative as documentation here:

INTO Clause
Stores the value of one or more columns for use later in another PROC SQL query or SAS statement.
https://support.sas.com/documentation/cdl//en/sqlproc/69822/HTML/default/viewer.htm#p0hwg3z33gllron1...
https://support.sas.com/documentation/cdl//en/sqlproc/69822/HTML/default/viewer.htm#p0hwg3z33gllron1...
TRIMMED
trims the leading and trailing blanks from values that are stored in a single macro variable.

[Tom]

Note there is a FEEDBACK button on that first link.  So I used it to send them your link to the page that does include the TRIMMED option.