DATA Step, Macro, Functions and more

proc sql and trim

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

proc sql and trim

[ Edited ]

Hi all,

 

I dcan't manage to use trim function in a proc sql, don't understand why.

I have a table A with a variable B which format is character(6):

I do this:

%global b;

PROC SQL NOPRINT;
	SELECT TRIM(B) INTO :b
	FROM A
	WHERE ROW_NUMBER = 1;
QUIT;
%put ___&b.___;

The result in the log is:

___A1    ___

I surely miss something, can you help me finding it?

 

Thanks


Accepted Solutions
Solution
‎07-24-2017 11:32 AM
Super User
Posts: 19,861

Re: proc sql and trim

When creating macro variables you need the TRIMMED option.

 

%global b;

PROC SQL NOPRINT;
	SELECT B INTO :b TRIMMED
	FROM A
	WHERE ROW_NUMBER = 1;
QUIT;
%put ___&b.___;

View solution in original post


All Replies
Solution
‎07-24-2017 11:32 AM
Super User
Posts: 19,861

Re: proc sql and trim

When creating macro variables you need the TRIMMED option.

 

%global b;

PROC SQL NOPRINT;
	SELECT B INTO :b TRIMMED
	FROM A
	WHERE ROW_NUMBER = 1;
QUIT;
%put ___&b.___;
Contributor
Posts: 34

Re: proc sql and trim

Many thanks for this.

Is it only because I declared the macrovariable before entering the PROC SQL?

It is strange this two ways of writing...

Super User
Super User
Posts: 7,988

Re: proc sql and trim

Really, as:

proc sql noprint;  
  select trim(" abc    ") into :r from sashelp.class where name="Alfred";
quit;
%put ___&r.___;

Works fine.  You may also be better off using strip() function rather than trim()

Super User
Super User
Posts: 7,076

Re: proc sql and trim

SAS stores character variables as fixed length variables. Since you didn't tell SQL what length to make the new variable you created with the TRIM() function call its best guess was to make it the same length as the variable going into the TRIM() function. So then when you pushed the value into a macro variable it included the spaces needed to pad the trimmed value back out to the full length of the variable.

 

You can use the TRIMMED keyword as part of the INTO clause to remove the trailing spaces.

proc sql noprint;
select b 
  into :b trimmed
  from a
  where row_number = 1
;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 189 views
  • 1 like
  • 4 in conversation