I am using sql query to remove the leading and trailing blanks then store in macro variables. but its not removing by using trim function. any alternate way to remove the trailing spaces before load into macro variable
18 Proc sql;
19 Select Button_name, TRIM(Trigger_button), Trigger_date into :Button_name, :Trigger_button, :Trigger_date
20 from ddf_cont.Master_timeline_trig
21 where Order_no=&order_no;
22 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.02 seconds
23
24 %put &Button_name &Trigger_button &Trigger_date;
3.4 3.4 Prepare and load data for PRESOP forms 06MAR2012
sunilreddy,
Are you sure you have trailing blanks? Perhaps you have leading blanks in &Trigger_date.
Try this variation, replacing the first mention of Trigger_date with:
strip(Trigger_date)
Good luck.
%let Button_name=%trim(&Button_name);
Hi There,
Leading and trailing blanks are not trimmed from values before they are stored in macro variables using the SQL into : method when creating a singular macro variable (as opposed to a list of macro variables or separated by a character where they are trimmed) as documented at http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543554.htm
If you want them to be removed then you need to add the following %let statements after your proc sql code as leading and trailing blanks on the right hand side of the equals sign are are ignored with the %let statement as documented here: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543704.htm
Proc sql;
Select Button_name, TRIM(Trigger_button), Trigger_date into :Button_name, :Trigger_button, :Trigger_date
from ddf_cont.Master_timeline_trig
where Order_no=&order_no;
quit;
%let button_name=&button_name;
%let trigger_button=&trigger_button;
%let tigger_date=&trigger_date;
Cheers,
Michelle
Michelle,
I shouldn't be, but I'm shocked to find that you're right. If anyone is interested, here's the test program I ran to confirm:
data test;
length city $ 30;
city='Boston';
run;
proc sql noprint;
select city, trim(city) into : all, : trimmed from test;
quit;
%put **&all** ##&trimmed##;
Thanks.
This has probably been documented in other posts....
With SAS 9.3 there is a new option allowed on the INTO clause - TRIMMED
which follows the macro name and will remove leading and trailing blanks
Proc SQL;
SELECT value INTO :var TRIMMED FROM work.dsn;
Quit;
INTO clause
What's new in the SAS 9.3 SQL Procedure
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.