topic Re: Data not getting load in to target because of length mismatch in SAS Data Management

Data not getting load in to target because of length mismatch

sas_pkc1 — Wed, 26 Apr 2017 12:21:47 GMT

Dear All,

I have stagging area in which length is 1000 and in taget length is 150

but all values are there which are below 150 character lenght

i am facing problem to load the data from stagging to target.

i have used and tried strip, trim, left still no change

using ORACLE to load the data.

Thanks,

Prashant.

Re: Data not getting load in to target because of length mismatch

RW9 — Wed, 26 Apr 2017 12:53:23 GMT

Care to elaborate? What is this "staging area", where do these lengths come in? How are you getting or sending data to Oracle?

I cannot see your computer, you need to explain.

Re: Data not getting load in to target because of length mismatch

Kurt_Bremser — Wed, 26 Apr 2017 13:09:56 GMT

If your "staging area" is a SAS library, you can use this method to reduce the length of a variable in a SAS dataset:

(supposing your variable/column is named "stringvar")

data new_dataset;
set original_dataset (rename=(stringvar=_stringvar));
length stringvar $150;
stringvar = _stringvar;
drop _stringvar;
run;

In the new dataset, the length of stringvar is now set to 150.

Re: Data not getting load in to target because of length mismatch

sas_pkc1 — Wed, 26 Apr 2017 13:41:22 GMT

SORRY FOR SHORT QUESTION

As we are working with SAS DI in that Source system is ORACLE AND Target is Data ware house which is in ORACLE.

WHAT IS HAPPENING IN ORACLE SOURCE SYSTEM THERE ARE VARIABLE WHOSE SIZE IS FOR EX: 1000 THEN WE ARE PERFORMING TRANSFORMATIONS AND LOADING DATA IN TO THE TARGET ORACLE.

THERE IS DIFFERENCE OF LENGHT BETWEEN SOURCE ORACLE TABLES AND TARGET ORACLE TABLES

SUPPOSE

SOURCE:

NAME LENGTH

PRASHANT 1000

TARGET:

NAME

PRASHANT 150

WE HAVE VALUES WHICH ARE LESS LENGHT IF WE COMPARE WITH TARGET.

I WANTED TO KNOW WHY DATA IS NOT GETTING LOAD IN TO THE TARGET TABLES.

I USED TRIM, STRIP, LEFT AND COMPRESS THEN ALSO DATA IS NOT MOVING FROM SOURCE TO TARGET.

PLEASE SUGGEST.

Re: Data not getting load in to target because of length mismatch

Kurt_Bremser — Wed, 26 Apr 2017 14:00:55 GMT

Since all this is happening in Oracle, bring this to the attention of the database administrators.

You don't need to shout at us, BTW. Writing in normal (not all-capitals) mode is perfectly acceptable.

Re: Data not getting load in to target because of length mismatch

RW9 — Wed, 26 Apr 2017 14:05:46 GMT

Ah, DI, I don't use that so maybe others can help. It could be any number of things, no data read from source system, transformations fail or result in no obs, no write to target for a number of issues. You must have some sort of steps to this, logs etc. may be nothing to do with a variable length Are there warnings/errors, does it state x number of obs returned from source, do the transformations work as intended, does the target return an error. Not much can really say as the question seems to be I have system X being transformed to system y and its not working - not really a Q&A topic, but more a system analysis.

Re: Data not getting load in to target because of length mismatch

LinusH — Mon, 01 May 2017 06:51:59 GMT

Question why the data isn't loaded. Is the column even mapped? If the target column is shorter than the source DIS issues a warning, unless you specify an expression.
So how did you do this? Also, sharing the log would be helpful.

Re: Data not getting load in to target because of length mismatch

Cynthia_sas — Mon, 01 May 2017 13:35:57 GMT

In addition, the SAS Studio forum would not really be the place to post your question. Most of the people who are using SAS Studio are using the free SAS learning software, which is not at all related to DI Studio.

There is a separate community for Data Management questions for folks using DI and DataFlux. That community is here: https://communities.sas.com/t5/Data-Management/ct-p/data_management

cynthia

Re: Data not getting load in to target because of length mismatch

Kurt_Bremser — Mon, 01 May 2017 14:02:55 GMT

I moved the thread to the SAS Data Managemment forum.

Re: Data not getting load in to target because of length mismatch

Patrick — Tue, 02 May 2017 11:30:33 GMT

@sas_pkc1

There is not a simple anwser to your question so just let me throw in a few thoughts and pointers.

The root issue here is the filed length difference betwen your staging table and your target table column. It appears that this column should map 1:1 from source to target. You say that the actual strings in this columns are shorter than the length in the target column - but that's not the solution to the issue.

Your source and target columns must match OR you have a clear business rule defined of how to transform the column (=read only the first n characters from source). I believe you have neither.

If you don't have a business rule for such a transformation then you need either to discuss with your data provider (the one providing the staging table) and the Interface Contract needs change so that the source column matches the target column OR you need to talk to the owner of the target model so that the column in the target model gets changed to match the source.

If you don't do this then you risk that one day a string comes which is actually 1000 characters long and you then truncate it when loading into target. You would be the one to be blamed if you've implemented something like this without a clear business rule.

Why is it not loading?

Highly likely because you're doing something wrong. What does the SAS log tell you?

How do you test? If you're using SQL Developer or the like then don't forget to issue a commit after insert/update/delete or you won't see the changes.

If source and target are in Oracle then I normally try and keep all processing in Oracle. How easily that's possible using DIS will depend on the transformation steps.

In order to give you advice you need as a first step to share the load log with us, a screenshot of the DIS flow section where this load happens and the generated code for this sections. We can't know what you've got wrong that this is not working.

As for the SAS log: Please add options sastrace=',,,d' sastraceloc=saslog nostsuffix; before the load step (ie. as pre-code) so we get more information in the SAS log.

Please provide SAS log and DIS generated code as text file attachments and don't just copy/paste it directly into this thread.