Data is not loading in SAS DI job

[MG18]

Hi All, 

I have created the SAS DI job which will the data from flat file to SAS data set () and then I am applying SCD type 2 on this data set to load it to oracle table PFB is screenshot of the Job :- 

Data is coming till fsk_risk_assessment_work data set but same is not loading to Oracle table and Giving below warning :- 

 Line 2: WARNING: Variable PROPOSED_RISK_CLASSIFICATION not appended because of type mismatch

when i checked type of PROPOSED_RISK_CLASSIFICATION  in SAS data set fsk_risk_assessment_work is character  and in fsk_risk_assessment (oracle table ) it is numeric and i have used INPUT(PROPOSED_RISK_CLASSIFICATION,$8.) in mapping also.

Please help me in the same.

[kiranv_]

try INPUT(PROPOSED_RISK_CLASSIFICATION,8.) . You need to use numeric informat.

For more read this small knowledge base below to understand better.

http://support.sas.com/kb/24/590.html

[MG18]

Hi @kiranv_ ,

I have tried the  INPUT(PROPOSED_RISK_CLASSIFICATION,8.) and  INPUT(PROPOSED_RISK_CLASSIFICATION,2.) still i am facing the same issue again.

[kiranv_]

please try best32.

[MG18]

Hi @kiranv_ , 

I have tried input(columnname,best32.) also but no luck.

[kiranv_]

can you show some sample data from the two columns you are mentioning  oracle and  SAS

[MG18]

I have used input(PROPOSED_RISK_CLASSIFICATION,1.) then it worked .

Thanks every one for helping me 🙂 .

[Patrick]

---

@MG18 wrote:

I have used input(PROPOSED_RISK_CLASSIFICATION,1.) then it worked .

Thanks every one for helping me 🙂 .

---

That doesn't make sense. All of the numeric formats should have worked (see below). If that's not the case then you'd probably should try and consult the generated code and log to understand why 1. works but best32. doesn't. For example if your column in source would have a length of char2 and contain a string like 1a then yes, 1. would work and best32. would throw an error - but only because you're not really reading the full string from source with 1.

Just not getting an error in the log doesn't ensure that you're getting the correct results.

data sample;
  length risk_classification $2;
  risk_classification='1';
  var_8=input(risk_classification,8.);
  var_best32=input(risk_classification,best32.);
  var_1=input(risk_classification,1.);
  output;
  stop;
run;
proc print data=sample;
run;

And on a side note:

I've made the experience that SCD2 Transformation generated code can be very inefficient when loading into Oracle - especially for full loads. You won't see this performance issues when loading the first time into an empty target table but it's eventually going to hit you in consecutive loads. So make sure you test for performance at an early stage with at least to cuts of source data and full volumes.

There are some options in the SCD2 loader which allow for better performing code but even if using these it's not always sufficient. We've ended up in more than one project I've been in to implement using a custom transformation for SCD2 loads into Oracle.

And two other tips which I've learned the hard way:

1: Make sure to always use DATE9. and DATETIME20. formats on your SAS date and datetime columns. If you use different SAS date(time) formats then you risk that the SAS/Access engine doesn't convert your SAS dates correctly to Oracle DATE or TIMESTAMP.

2: If changing metadata tables definitions (i.e. a format) on tables which don't get always physically recreated by the DIS process then make sure that you also change these definitions on the underlying physical table. Once the physical table has been created a change in SAS metadata won't automatically change the physical table (unless recreated) but when you execute the DIS generated code then the code will during execution time often use what's in the physical table and not what's defined in table metadata.

[kiranv_]

excellen points by  @Patrick