BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SYN
Obsidian | Level 7 SYN
Obsidian | Level 7

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.

Sreenivasa
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

4 REPLIES 4
Patrick
Opal | Level 21

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.

SYN
Obsidian | Level 7 SYN
Obsidian | Level 7

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

Sreenivasa
Patrick
Opal | Level 21

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.

SYN
Obsidian | Level 7 SYN
Obsidian | Level 7

Thank you very much Patrick.

Sreenivasa

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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