04-26-2017 08:21 AM
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.
04-26-2017 08:53 AM
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.
04-26-2017 09:41 AM
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
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.
04-26-2017 10:00 AM
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.
04-26-2017 10:05 AM
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.
05-02-2017 07:30 AM - edited 05-02-2017 07:30 AM
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.
04-26-2017 09:09 AM
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.
05-01-2017 02:51 AM
05-01-2017 09:35 AM
05-01-2017 10:02 AM