Writing data to Teradata

[saspert]

Hi,

I have an issue writing data to teradata. Please review my code. The idea is to take all the data from a dataset and create a volatile table in teradata. This volatile table is then used in a number of proc sql blocks later on in the code.

All the proc sql blocks return 0 records and my colleagues are guessing it may be because of the format for this one column MCID. The MCID in teradata is character (for our purpose). But the FINALMBR dataset has it as numeric. So, my code (below) replaces the old code (in comments).

libname &userid clear;
LIBNAME &userid TERADATA user="&userid.@LDAP" password=&password TDPID=DWPROD2 CONNECTION=GLOBAL DBMSTEMP=YES;

DATA FINALMBRmcids (drop=MCIDold);
SET &FLDR..FINALMBR (keep=MCID rename=(MCID=MCIDold));
MCID=put(MCIDold,11.);
RUN;

DATA &userid..FINALMBRmcids;
SET FINALMBRmcids ;
RUN;
/*
DATA &userid..FINALMBRmcids;
SET &FLDR..FINALMBR (keep=MCID);
RUN;
*/

But the issue with the proc sql blocks returning 0 records is still there. Any suggestions would be appreciated (maybe alternative ways of loading teradata).

Thanks,

saspert

[Tom]

Perhaps it is not Teradata but the way you are converting your numbers into character strings?

Are the numbers all 11 digits long?  If not then does Teradata have the spaces in the front of the field or the end of the field?  Perhaps Teradata has trailing spaces stored in the data. SAS will normally do an implied trim when loading character variables into variable length text fields in databases.

[saspert]

Hi Tom,

You may have a point there. MCID does not have all of them 11 digits long. Most of them are less than 11 digits. So, are you suggesting that I should remove both leading and trailing spaces before loading into the database?

Thanks,

saspert

[Tom]

If you try to put the number 1234 into a 11 character text field you could store it as four digits followed by 7 spaces or as 7 spaces folllowed by 4 digits.  Using MCID=put(MCIDold,11.); would result in MCID='       1234'.  If you want instead to have it stored in SAS as '1234' then you can add the -L format modifier.  MCID=put(MCIDold,11.-L);

In SAS it doesn't matter if I set MCID='1234' or '1234       '  as SAS will store them the same and do comparisons the same.If you load it into a VARCHAR field in Teradata then SAS witll strip off the trailing spaces.  However you might have data in the other table that has the trailing spaces. So you may need to use STRIP() or TRIM() functions in your compaison operators when trying to use you new table in a query.

For example:
proc sql ;

  create table &userid..MATCHS as

    select a.*

    from &userid..MASTER a

       , &userid..FINALMBRmcids b

    where trim(b.mcid) = trim(a.mcid)

  ;

quit;

[LinusH]

Have you opened FinalMbr and verified that MCID is stored correctly?

What is the syntax of your SQL blocks?

[saspert]

Hi LinusH,

I did look at the dataset - it is stored as numeric.

Thanks,

saspert

[TomKari]

1. I don't have access to SAS right now, so I can't check it, but is it possible that if your number is less than 11 digits, SAS will create a smaller variable? You could add a LENGTH MCID $11; statement before your assignment.

2. Could be you can't see your data because you haven't issued a COMMIT to Teradata?

3. Definitely take a look at &userid.FINALMBRmcids in Teradata, and if possible check out the metadata to ensure your fields are defined correctly. If you can't access Teradata direcly, start with

Select count(*) from &userid.FINALMBRmcids;

in PROC SQL to see how many records are there, and then pull a few or all of them into a SAS dataset to see what they look like.

Tom

[saspert]

Hi TomKari,

for your suggestions -

1) Is your question similar to Tom's question above?

2) Not sure why commit would come up as we are not sure a sql/proc sql block.

3)I may not be able to see the table inside teradata but I think it looks okay from the sas log -

NOTE: The data set AXXXX2.FINALMBRmcids has 57302 observations and 1 variables.

NOTE: DATA statement used (Total process time):

      real time           19:38.64

      cpu time            3.08 seconds

[TomKari]

1. No, there are two issues. a) is SAS creating MCID as a character variable with a length of 11, or if the outcome of the PUT function is less than 11 characters on the first record, SAS might be creating it shorter. b) (Tom's question, I think) If MCID is 11 characters, how is the PUT function placing the numeric digits in the variable...with spaces at the front, or at the back.

2. Just fishing with the COMMIT question, I've bumped into it with other DBMS products, and I've never used Teradata.

3. I agree, with your LOG snippet the dataset looks good.

What about running some code like the following, to see what your numbers look like?

set &userid.FINALMBRmcids(obs=100);

run;

proc print;

run

That might tell you if your numbers are in the correct format.

Tom

[shivas]

Hi ,

This would be problem with the user permissions that you are using to connect to teradata database.

Try using DBC or admin user in you libname statment and check (you can see all the tables that you have created in teradata)

Thanks,

Shiva