topic Re: Don't want to remove trailing spaces in SAS Procedures

Don't want to remove trailing spaces

Hercules — Sat, 16 May 2015 19:26:26 GMT

Ok,

So I am trying to create a table in Teradata from Oracle using SAS.

When I do that, SAS by default remove the trailing spaces, but I want those spaces.

For example:

If I have a value as 026... (dot representing spaces here)

SAS load them into a Teradata table as : 026

How can I achieve that?

Thanks in advance.

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 05:20:28 GMT

Specify enough length for that variable , sas will add blanks after it automatically .

length var $ 20 ;

Xia Keshan

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 06:32:36 GMT

Hello,

Thanks for the quick reply. Not even that working.

I tried first converting spaces into hashes by using TRANWRD and then replacing # by spaces again.

But when I do that, SAS converts all the non occupied places as spaces whether it was in the column value or not.

Approach I'm using is- Tranwrd(column1,"20"x,"#")

for eg, if the column1 has some values and length as 10 $

(Representing dot as spaces)

COLUMN1

Anant...

Sas.

Oracle

First it converts like-

Anant#####

Sas#######

Oracle####

But it should be like,

Anant###

Sas#

Oracle

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 07:11:42 GMT

sas will pad the blanks at the end of value until it reach the length of variable.So you will get

Anant#####

Sas#######

Oracle####

NOT

Anant###

Sas#

Oracle

Re: Don't want to remove trailing spaces

SASKiwi — Sun, 17 May 2015 07:17:12 GMT

I'm curious to know the significance of the trailing blanks? If you are doing value comparisons you don't normally add trailing blanks if x = '026 '.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 07:23:46 GMT

My target column is a UPI in Teradata.

That's why it is discarding the other value I.e. 026 with blanks.

As there are two values of 026 and 026 with spaces.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 07:35:45 GMT

Then how can I have trailing spaces?

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 07:46:24 GMT

How do you load them into a Teradata table ?

If it is SQL , specify its length as

select var length=10 ......... from ....

If it was data step, specify its length as

length var $ 10 ;

Re: Don't want to remove trailing spaces

jakarman — Sun, 17 May 2015 08:01:44 GMT

Change the question as it as about SAS-Teradata not only SAS. TD (Teradata) is a RDBMS having his own datatypes a tri-value-logic with nulls and different date-time formats supporting interval according to SQL:2013 whereas SAS is using SQL:99. That are a lot of differences.

Look at the SAS-TD access translation SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition char and varchar from TD to SAS are the same. Going from SAS to TD the default is only CHAR

Review the TD varchar definition that one is removing trailing blanks when loading loading data. The active hardware compression for blanks at char fields having spaces could be as effective.

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Load_and_Unload_Utilities/B035_2411_071A/2411Ch03.026.029.html

Remember TD is a special one. Loading data to TD can have issues, they will not return as errors instead some dedicated tables are created having the erroneous ones.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 08:57:01 GMT

Hello Jaap,

I agree with you, but not only Teradata, even if I am making a data set in SAS, then also the spaces are not coming. Its like SAS is removing trailing spaces by default.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 09:01:25 GMT

I have tried that Xia,

That is also not working.

Re: Don't want to remove trailing spaces

jakarman — Sun, 17 May 2015 09:03:15 GMT

No SAS is not removing the spaces, in contrary spaces are always added to fill it up to full fixed storage length. The fixed-storage like the char in TD.

This behavior is the reason why you need to trim (remove trailing spaces) before you can effective concatenate strings. Fixed=fixed there must be something there.

You must have some other issue. Describe it better with preferable some code showing that.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 09:09:39 GMT

I just wrote a small data step.

Data;

Col1= 'Anant ';

Col2= length(col1);

run;

When I copy paste the value Anant in a notepad, there is no trailing space.

also the length has given me 5 as output.

But it should be 10 (as I've given 5 spaces), right?

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 09:45:09 GMT

data x;

Col1= 'Anant ';

Col2= lengthc(col1);

put Col2=;

run;

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 09:49:51 GMT

Yes that is OK, but I don't only want to find the length.

my main concern is to read these two as different values.

If you write,

proc SQL;

select distinct col1 from x;

run;

Then the output will be-

Col1

Anant

But these are two different values.

Re: Don't want to remove trailing spaces

jakarman — Sun, 17 May 2015 09:58:32 GMT

SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition finding the position of the last non-blank is length. There are more functions like the c (character) m (bytes)

The length of missing character all blank is either 0 or 1. That could be a confusing one as is with NULLS.

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 10:20:10 GMT

Yes Jaap, that is helpful.

But what if I want to select distinct values?

If I am using this:

Proc SQL;

select distinct col1 from data1;

quit;

Then the output is:

Col1

Anant

Which actually should be:

Col1

Anant

Anant...... (Considering dots as spaces)

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 11:19:01 GMT

You can't ask to do that . why not replace the trailing blanks with other blank character like TAB ?

if <condition is true> then column1=Tranwrd(column1,"20"x,"09"x)

Re: Don't want to remove trailing spaces

Hercules — Sun, 17 May 2015 11:43:26 GMT

That's what I was trying to do.

See my second post.

When I ma trying to replace spaces by # then its is giving me all the hashes.

Like for col1 length 10 (representing dots as spaces)

Col1

Anant...

Sas

Hello..

If I am using tranwrd then:

Col1

Anant#####

Sas#######

Hello######

But ideally it should be

Anant ###

Sas

Hello##

Re: Don't want to remove trailing spaces

Ksharp — Sun, 17 May 2015 12:39:20 GMT

You need specify a condition . Which one of Anant should be distinguished ? sas take all of Anant as the same one .