BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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

5 REPLIES 5
Astounding
PROC Star

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.

Ksharp
Super User

%let Button_name=%trim(&Button_name);

MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
Astounding
PROC Star

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.

Jay_TxOAG
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2464 views
  • 0 likes
  • 5 in conversation