BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hercules
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Hercules
Fluorite | Level 6

First of all thank you everyone for taking out their valuable time and help me out to solve my problem.

Thanks Tom for giving me an idea to deal it on Oracle level by specifying another column of length.

I am not making that space value column as my UPI in landing, but the UPI in fact in landing is a REC_ID column which is a surrogate key.

UPI for that column has been made in the staging area.

Pardon me if I did something wrong as I am a fresher.

I solved the problem by executing a simple pass through query.

This is how I did it.

Correct me if I am wrong but for now the problem has been solved and if anyone wishes the same to replicate the data from the source to the target as the way it was stored in source then one may follow this:

Problem:

Trailing spaces in a column at Oracle source end but not coming in target Teradata table.

Solution:

1. Pass through in Oracle, replace space by '$', insert into Teradata target table.

2. Again pass through in Teradata, Write an update query to replace '$' with spaces.

proc sql;

connect to oracle(......);

insert into tera.tbl1

select * from connection to oracle

(select col1

, replace(col2,' ','$') as col2

, col3

from schema.tbl2

);

disconnect from oracle;

connect to teradata(.....);

execute( update schema.tbl2

set col2 = oreplace(col2,'$',' ')

)by teradata;

execute(commit);

disconnect from teradata;

quit;

View solution in original post

32 REPLIES 32
Ksharp
Super User

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

length var $ 20 ;

Xia Keshan

Hercules
Fluorite | Level 6

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

Ksharp
Super User

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

Hercules
Fluorite | Level 6

Then how can I have trailing spaces?

SASKiwi
PROC Star

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

Hercules
Fluorite | Level 6

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.

Ksharp
Super User

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 ;

Hercules
Fluorite | Level 6

I have tried that Xia,

That is also not working.

jakarman
Barite | Level 11

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

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.

---->-- ja karman --<-----
Hercules
Fluorite | Level 6

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.

jakarman
Barite | Level 11

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. 

---->-- ja karman --<-----
Hercules
Fluorite | Level 6

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?

Ksharp
Super User

data x;

Col1= 'Anant     ';

Col2= lengthc(col1);

put Col2=;

run;

Hercules
Fluorite | Level 6

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 32 replies
  • 3438 views
  • 6 likes
  • 5 in conversation