SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Taking History from sas tables to sas tables in DI 3.4

Accepted Solution Solved
Reply
Contributor SYN
Contributor
Posts: 39
Accepted Solution

Taking History from sas tables to sas tables in DI 3.4

I have three 3 csv source files and created 3 target tables.

I got the data in 3 target tables using my DI studio 3.4 job.

These three target tables will replace every month with fresh data.

1st table will replace with latest last 36 months of fresh data.

2nd and 3rd tables will replace with last 1 month of fresh data.

My requirement is I have to take history from these 3 tables and put it in 3 target history tables.

1st table I have to move last 1 month data(means 36th month data) to history and next month i need to append the data in hisrory and so on.

2nd and 3rd tables every month move data to history table and next month onwards append the data in history table.

Can you please help me which approach is good for 1st table and which approach is good for 2nd and 3rd table.

Thanks in advance.


Accepted Solutions
Solution
‎06-19-2012 10:53 AM
Respected Advisor
Posts: 3,886

Re: Taking History from sas tables to sas tables in DI 3.4

On a high level and just meant to give you some food for thought:

- For your first table get the max. date in your source table (select max(date) into :max_date). Then load from your target table all rows older than max_date minus 35 months into your history table (use intnx() to calculate this threshold date). I would load data into the history target table using update/insert(append) so that you could run the process more than once with the same data without duplicating data. I then would use a full replace of the target table with the new 36month source data.

- For table 2 & 3:

Assuming the actual data have only 1 month worth of data: Get the max. date from the source and then load everything from the target table which has a date max_date minus 1 month (again use intnx() for this) into the history target table using update/insert. Then replace the target table with the new source data.

If you need to store more than one month in the target table: similar approach but instead of replacing the target table you update/insert new data from source and you delete data moved to history target. Use "reuse" when loading tables where you also delete rows - or the table will grow much larger than necessary.

No more sure about what DIS3.4 provides you as standard transformations (eg. update/insert loading as part of table loader as available in DIS4.x). I assume that you will have to hand-code quite a bit for the above approach - eventually creating a user transformation for update/insert loading.

View solution in original post


All Replies
Respected Advisor
Posts: 3,886

Re: Taking History from sas tables to sas tables in DI 3.4

An approach I tend to take in such situations (if data volumes are not too high):

I create for every month of history a separate table (<table name>_<mmyyyy>). Then I query dictionary.tables and generate a view over all existing history tables (3 views in your case). It's then the view which I register as metadata and provide to users.

Such an approach also allows for possible later requirements like 12 months roling history tables and the like.

You will need to code this and you need also to handle cases where you have to re-run your jobs with the same data (e.g. re-loading corrected data) -> the month part in the name for history tables is best derived from the date in the data of such a table and not based on when you run it.

As a best practice: You should have a separate DI job per target table.

Contributor SYN
Contributor
Posts: 39

Re: Taking History from sas tables to sas tables in DI 3.4

Thank you Patrik,

We taken the approach like below.

1st source SAS table(Fresh 36 months data)     -     Target History SAS table(need to extract only 1 month data ie last 36th month data)

Please help me to get only last 36th month data into target table.

Every month source will get 3 years of data(36 months data). Need to extract last one month(36th month data to target table).

2nd and 3rd SAS source tables(Fresh 1 month data)    -     Target history SAS tables will take one month data before loading fresh data into source tables. Please help me to move data every month to history and next month it has to append.

Please help me for these two approaches. Thanks in advance

Solution
‎06-19-2012 10:53 AM
Respected Advisor
Posts: 3,886

Re: Taking History from sas tables to sas tables in DI 3.4

On a high level and just meant to give you some food for thought:

- For your first table get the max. date in your source table (select max(date) into :max_date). Then load from your target table all rows older than max_date minus 35 months into your history table (use intnx() to calculate this threshold date). I would load data into the history target table using update/insert(append) so that you could run the process more than once with the same data without duplicating data. I then would use a full replace of the target table with the new 36month source data.

- For table 2 & 3:

Assuming the actual data have only 1 month worth of data: Get the max. date from the source and then load everything from the target table which has a date max_date minus 1 month (again use intnx() for this) into the history target table using update/insert. Then replace the target table with the new source data.

If you need to store more than one month in the target table: similar approach but instead of replacing the target table you update/insert new data from source and you delete data moved to history target. Use "reuse" when loading tables where you also delete rows - or the table will grow much larger than necessary.

No more sure about what DIS3.4 provides you as standard transformations (eg. update/insert loading as part of table loader as available in DIS4.x). I assume that you will have to hand-code quite a bit for the above approach - eventually creating a user transformation for update/insert loading.

Contributor SYN
Contributor
Posts: 39

Re: Taking History from sas tables to sas tables in DI 3.4

Thank you very much Patrick.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 313 views
  • 0 likes
  • 2 in conversation