DATA Step, Macro, Functions and more

Remove Spaces

Reply
SAS Employee
Posts: 73

Remove Spaces

Good Afternoon,

I have column name ‘modified’ which is character type with Length (in bytes) of 11.  Not all the value stored has 11 characters.  When I try to filter table by modify value I always have to include space for filter to work.

For example: if the value is ‘ABCD’ then for filter I would have to include ‘ABCD       ‘ (7 empty space). 

How can I remove these leading spaces from table or source file?

Thanks in advance.

Super User
Posts: 17,784

Re: Remove Spaces

You say filter, are you in EG or somewhere else?


The strip() or trim() function can be used to remove spaces from a variable.

SAS Employee
Posts: 73

Re: Remove Spaces

Reeza,

I'm using EG and strip() or trim() didn't not work.

Create table test as

SELECT DISTINCT t1.Modified,right(trim(t1.Modified)) as trim_Value,

FROM Work.table t1;

EG Code Generated for filter:

Select * From test

WHERE t1.trim_Value = '00000102     '

;

Respected Advisor
Posts: 3,124

Re: Remove Spaces

You have to do it on the fly, otherwise, the new variable you just made will inherit the original length, meaning it will still have the trailing blanks. So try this:

SELECT DISTINCT t1.Modified,right(trim(t1.Modified)) as trim_Value,

FROM Work.table t1;

EG Code Generated for filter:

Select * From test

WHERE strip( t1.Modified) = '00000102'

;

Haikuo

SAS Employee
Posts: 73

Re: Remove Spaces

Is it possible to stored the date without trailing blank ?

Respected Advisor
Posts: 3,124

Re: Remove Spaces

Yes, define a shorter length for your new variable.

SELECT DISTINCT t1.Modified,left(t1.Modified)) as trim_Value length=8,

FROM Work.table t1;

Haikuo

Super User
Posts: 10,483

Re: Remove Spaces

Maybe this example will show that the spaces aren't necessarily in the data but how the variable is referenced can create the added spaces. Ifyou run the code below the results will show significantly different values for the LONGSTR variable, one with spaces that were not assigned to the variable A. For some uses SAS will pad the variable to it's length with spaces.

 

data _null_;

file print;

length a $ 8 longstr $ 50;;

a = 'abc';

longstr = 'This '||a||'string';

put longstr=;

longstr = 'This '||strip(a)||'string';

put longstr=;

run;

Super User
Super User
Posts: 6,499

Re: Remove Spaces

I am not very familiar with EG, but if that is SAS code then it doesn't make sense. In SAS if I test if variable X = 'TEXT' it will be true whether X is defined as length 4 or 4000.  The trailing spaces are ignored.

Is it possible that the coders for EG have ignored 30 years of SAS experience on how to compare character variables?

Respected Advisor
Posts: 3,124

Re: Remove Spaces

I suppose OP is confused on Leading/Trailing blanks. In the first post, "leading spaces" did have been mentioned, although the example presented is about "Trailing blanks".

Haikuo

SAS Employee
Posts: 73

Re: Remove Spaces

Sorry for the confusion. It's trailing space not the leading spaces.

Hai.Kuo - i can't define shorter length because some values are 8 space where others are 11.,

Super User
Posts: 17,784

Re: Remove Spaces

Clean up the source data by applying the trim function, then try it again.

Respected Advisor
Posts: 3,124

Re: Remove Spaces

Then you will have 3 blanks for those have 8, unless you want to truncate those have 11. I understand you are learning the concept, but I can't fathom the reason why that matters practically?

Also, if it is only trailing blanks, then you will have to answer the question from Tom. When doing comparison, trailing blanks should NOT matter at all, even in EG.

Haikuo

Respected Advisor
Posts: 3,887

Re: Remove Spaces

@Tom

SAS EG is mainly a "SAS code generator" having a lot of wizards helping you to generate valid code (eg. a query builder) which then gets executed on the server you choose.

I've just tried using the EG query builder and there is no issue with trailing blanks.

Super Contributor
Posts: 376

Re: Remove Spaces

newbi wrote:

Reeza,

I'm using EG and strip() or trim() didn't not work.

Create table test as

SELECT DISTINCT t1.Modified,right(trim(t1.Modified)) as trim_Value,

FROM Work.table t1;

EG Code Generated for filter:

Select * From test

WHERE t1.trim_Value = '00000102     '

;

The right() function is suspicious.  This would right justify a character variable, which would create leading spaces, which *are* significant.

Try this:

create table test as

SELECT DISTINCT t1.Modified,strip(t1.Modified) as trim_Value

FROM Work.table t1;


This should then work, regardless of whether EG is padding the generated values with trailing spaces - trailing spaces are not significant:

Select * From test

WHERE t1.trim_Value = '00000102     '

;

Of course, you could just use the function directly in the where clause:

Select * From test

WHERE strip(t1.trim_Value) = '00000102     '

;


But since that is EG generated code...

I suspect your source data has character data that contains leading spaces.  Or sometimes non-printing control characters can be problematic; you could try reviewing your data using the $hex. format.

Hope this helps...

Regular Contributor
Posts: 195

Re: Remove Spaces

I think it's very simple if your charcter variable includes leading or trailing blanks...

WHERE compress(var_name) = "Compare Value";

Ask a Question
Discussion stats
  • 17 replies
  • 614 views
  • 0 likes
  • 10 in conversation