topic EG trailing blanks issue in SAS Enterprise Guide

EG trailing blanks issue

sunilreddy — Mon, 27 Feb 2012 15:42:09 GMT

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

24 %put &Button_name &Trigger_button &Trigger_date;

3.4 3.4 Prepare and load data for PRESOP forms 06MAR2012

EG trailing blanks issue

Astounding — Mon, 27 Feb 2012 16:39:01 GMT

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.

EG trailing blanks issue

Ksharp — Tue, 28 Feb 2012 09:05:56 GMT

%let Button_name=%trim(&Button_name);

EG trailing blanks issue

MichelleHomes — Tue, 28 Feb 2012 12:07:59 GMT

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

EG trailing blanks issue

Astounding — Tue, 28 Feb 2012 14:15:22 GMT

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.

Re: EG trailing blanks issue

Jay_TxOAG — Wed, 21 Nov 2012 15:49:00 GMT

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

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n1tupenuhmu1j0n19d3curl9igt4.htm

What's new in the SAS 9.3 SQL Procedure

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#sqlprocwhatsnew93.htm