Hi all,
I dcan't manage to use trim function in a proc sql, don't understand why.
I have a table A with a variable B which format is character(6):
I do this:
%global b; PROC SQL NOPRINT; SELECT TRIM(B) INTO :b FROM A WHERE ROW_NUMBER = 1; QUIT; %put ___&b.___;
The result in the log is:
___A1 ___
I surely miss something, can you help me finding it?
Thanks
When creating macro variables you need the TRIMMED option.
%global b; PROC SQL NOPRINT; SELECT B INTO :b TRIMMED FROM A WHERE ROW_NUMBER = 1; QUIT; %put ___&b.___;
When creating macro variables you need the TRIMMED option.
%global b; PROC SQL NOPRINT; SELECT B INTO :b TRIMMED FROM A WHERE ROW_NUMBER = 1; QUIT; %put ___&b.___;
Many thanks for this.
Is it only because I declared the macrovariable before entering the PROC SQL?
It is strange this two ways of writing...
Really, as:
proc sql noprint; select trim(" abc ") into :r from sashelp.class where name="Alfred"; quit; %put ___&r.___;
Works fine. You may also be better off using strip() function rather than trim()
SAS stores character variables as fixed length variables. Since you didn't tell SQL what length to make the new variable you created with the TRIM() function call its best guess was to make it the same length as the variable going into the TRIM() function. So then when you pushed the value into a macro variable it included the spaces needed to pad the trimmed value back out to the full length of the variable.
You can use the TRIMMED keyword as part of the INTO clause to remove the trailing spaces.
proc sql noprint;
select b
into :b trimmed
from a
where row_number = 1
;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.