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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.