Desktop productivity for business analysts and programmers

EG trailing blanks issue

Reply
Frequent Contributor
Posts: 115

EG trailing blanks issue

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

Super User
Posts: 5,366

EG trailing blanks issue

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.

Super User
Posts: 9,867

EG trailing blanks issue

%let Button_name=%trim(&Button_name);

Trusted Advisor
Posts: 1,295

EG trailing blanks issue

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

Super User
Posts: 5,366

EG trailing blanks issue

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.

Contributor
Posts: 45

Re: EG trailing blanks issue

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#n1tupenuhmu1j0n19d...

What's new in the SAS 9.3 SQL Procedure

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

Ask a Question
Discussion stats
  • 5 replies
  • 1511 views
  • 0 likes
  • 5 in conversation