BookmarkSubscribeRSS Feed
newbi
SAS Employee

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.

17 REPLIES 17
Reeza
Super User

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


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

newbi
SAS Employee

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     '

;

Haikuo
Onyx | Level 15

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

newbi
SAS Employee

Is it possible to stored the date without trailing blank ?

Haikuo
Onyx | Level 15

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

ballardw
Super User

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;

Tom
Super User Tom
Super User

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?

Haikuo
Onyx | Level 15

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

newbi
SAS Employee

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

Reeza
Super User

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

Haikuo
Onyx | Level 15

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

Patrick
Opal | Level 21

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

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
UrvishShah
Fluorite | Level 6

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

WHERE compress(var_name) = "Compare Value";

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!

How to Concatenate Values

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.

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
  • 17 replies
  • 4802 views
  • 0 likes
  • 10 in conversation