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.
You say filter, are you in EG or somewhere else?
The strip() or trim() function can be used to remove spaces from a variable.
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 '
;
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
Is it possible to stored the date without trailing blank ?
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
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;
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?
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
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.,
Clean up the source data by applying the trim function, then try it again.
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
@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.
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...
I think it's very simple if your charcter variable includes leading or trailing blanks...
WHERE compress(var_name) = "Compare Value";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.