topic Re: Remove Spaces in SAS Programming

Remove Spaces

newbi — Wed, 29 May 2013 17:33:38 GMT

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.

Re: Remove Spaces

Reeza — Wed, 29 May 2013 17:44:15 GMT

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

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

Re: Remove Spaces

newbi — Wed, 29 May 2013 18:06:00 GMT

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 '

;

Re: Remove Spaces

Haikuo — Wed, 29 May 2013 18:42:58 GMT

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

Re: Remove Spaces

newbi — Wed, 29 May 2013 20:30:52 GMT

Is it possible to stored the date without trailing blank ?

Re: Remove Spaces

Haikuo — Wed, 29 May 2013 20:39:39 GMT

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

Re: Remove Spaces

Tom — Wed, 29 May 2013 20:56:24 GMT

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?

Re: Remove Spaces

Haikuo — Wed, 29 May 2013 21:02:13 GMT

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

Re: Remove Spaces

newbi — Wed, 29 May 2013 21:05:42 GMT

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

Re: Remove Spaces

Reeza — Wed, 29 May 2013 21:17:04 GMT

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

Re: Remove Spaces

Haikuo — Wed, 29 May 2013 21:27:26 GMT

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

Re: Remove Spaces

ballardw — Wed, 29 May 2013 22:48:56 GMT

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;

Re: Remove Spaces

ScottBass — Thu, 30 May 2013 09:13:50 GMT

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

Re: Remove Spaces

UrvishShah — Thu, 30 May 2013 11:04:18 GMT

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

WHERE compress(var_name) = "Compare Value";

Re: Remove Spaces

statguy22 — Thu, 30 May 2013 15:25:03 GMT

If you google "SAS Character Functions", there is an excellent .pdf file from the SAS support web site that explains all of the character functions in SAS (including functions that eliminate various types of spaces).

Re: Remove Spaces

ScottBass — Sun, 02 Jun 2013 09:07:51 GMT

This would not give the correct results.

Try WHERE compress(" Compare Value ") = "Compare Value" to see what I mean.

Re: Remove Spaces

Patrick — Sun, 02 Jun 2013 10:38:04 GMT

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

Re: Remove Spaces

TomKari — Sun, 02 Jun 2013 17:34:09 GMT

You shouldn't be having a problem with this. Here's an example that works for me in EG:

1. Bring SASHELP.SHOES into your project. If you check the properties for the dataset, the Product field is 14 long.

2. Start the Filter and Sort task against SASHELP.SHOES.

3. Select all of the columns for the result, and select the Filter tab. In Filter, set Product Equal to Boot (note the capitalization matters.) Don't change anything else, and run the query. It should work fine.

The filter in Query Builder works on this example as well.

Let us know what happens when you try this.

Tom