topic Re: creating macro variables via proc sql-remove spaces in SAS Programming

creating macro variables via proc sql-remove spaces

Ronein — Tue, 25 Sep 2018 06:03:59 GMT

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.;

Re: creating macro variables via proc sql-remove spaces

Kurt_Bremser — Tue, 25 Sep 2018 06:34:57 GMT

Simply add

%let No_words=&No_words;

%let removes leading and trailing blanks.

Re: creating macro variables via proc sql-remove spaces

Oligolas — Tue, 25 Sep 2018 06:35:12 GMT

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.;

Re: creating macro variables via proc sql-remove spaces

ajs_rdg — Wed, 02 Oct 2019 13:31:43 GMT

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&docsetVersion=9.4&locale=en
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#p0hwg3z33gllron184mzdoqwpe3j.htm
https://support.sas.com/documentation/cdl//en/sqlproc/69822/HTML/default/viewer.htm#p0hwg3z33gllron184mzdoqwpe3j.htm#n1tupenuhmu1j0n19d3curl9igt4
TRIMMED
trims the leading and trailing blanks from values that are stored in a single macro variable.

Re: creating macro variables via proc sql-remove spaces

Tom — Wed, 02 Oct 2019 13:38:40 GMT

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.