Load Data into Target by comparing with Source Table based on Date column

[JJP1]

Hi Team,
Iam using table loader as append to existing option to load daily data with source date has today()-1.
So i have a scenairo where to check whether for example source_date say 18NOV2019 and this is already loaded to target table .
Due to some situations again the 18NOV2019 data has come from source.
So at this time it should delete the alreday loaded old data for 18NOV2019 in target table and then reload with new data for 18NOV2019 from source.

Would it be possible to make use of table loader transformation as iam already using this one in SAS job please. ?

[LinusH]

If your requirement is to remove all records for a date prior to loading, then you probably need to do this in two steps, first a conditional (?) SQL Delete, then Append using Table Loader. This will hold true if you are not sure if there might be different no of records (set of identifiers) between the loads.

[JJP1]

Hi @LinusH ,

the requirement is not to remove all records for a date prior to loading .
why i asked like this is sometimes data will be getting loading into target from source,which is actually incorrect.
so in this case it is difficult to manually remove those records from target.
it would be good if it is handled by SAS job itself
Would you please suggest how can we do this please

[LinusH]

To be able to respond, you need to be crystal clear when these situation occur, how you are detecting/marking it, and the specific deletion criteria.

In general terms, when you identify this situation, you can load the necessary information into macro variable(s), have a conditional (use the transformations under Control) Delete transformation in which you use these macro variables.

[JJP1]

Thanks @LinusH .

please find the details below :

Would you please help if i use delete trasnformation how this will compare the data in target table based on source date whether it is alreday loaded or not ? if alreday loaded then delete only those data records based on date column.

This scenario will happen if we are getting wrong data for one date say "19NOV2019" and target table is alreday loaded with incorrect data for 19NOV2019.So in this case source system will run again to get the correct data for 19NOV2019.so again SAs jobs needs to be processed with correct data.

Would you please help completly please how can i approach using SAS DI Jobs please.iam thinking on table looader option.

but  iam ready to take your suggestions please

[Patrick]

@JJP1 

If you simply need to update data in target with a matching key (same date and other key variables) then configuring the table loader to use Update & Insert should do the job. 

If you need processing that also deletes data from target after a previous load with wrong data (meaning the bad load had also rows with incorrect key variables) then I can thing of the following options:

A) You get a separate file for deletes from source and you use a SQL Delete for this data and then a table loader with Update & Insert

B) You execute a delete for all target data with a date key of the load you need to redo, then use the table loader with Insert only

C) If you just get the next day a single file with new records (today's date) and with correction records (today-n date) then a table loader with Update & Insert should do the job.

So you see: It won't be that hard to come up with a solution but for us to tell you what's going to work, it's very important that you're very precise how your source data looks like.

Also:

If your target table is a SAS table and such correction are regular then you should also implement some housekeeping process which re-creates the table from time to time. Reason: A SQL Delete for SAS tables only deletes the row logically but it remains in the physical file (the SAS table). Only re-creating the file will remove logically deleted rows and though reduce the file size (less storage, improved performance).

If your target table resides in a database then you don't have to recreate it BUT it both source and target table are in the database then consider using the SQL Merge transformation instead of the Table Loader. A SQL Merge should also allow to cover case B) like: SQL Merge for Update & Delete (all keys with an "old" date), SQL Insert of all keys with a current data.

[JJP1]

Thanks @Patrick .

Actually i forgot to mention that i need to build SAS dataset which should take care of historical data also.
So currently i am using table loader with "Append to Existing" Option and iam sure that there will not be any duplicates.
So i can not use update/insert option if we are building a history dataset.
So may i request to suggest any other option  to take care of delete scenario along by using table loader (Append to existing option)for me please.

[Patrick]

@JJP1 

"i forgot to mention that i need to build SAS dataset which should take care of historical data also"

And again, you need to be very precise how you want to maintain history. You also need to be very precise how you could get corrected records - like could it be possible that you get such a correction for a date current-2 while you've already loaded a new row for the same key for current-1.

Don't focus which table loader you need/want to use. First fully define your data organisation (SCD2, separate current and history table, record versioning, ....) and the load process on a logical level required to maintain your tables. Only then start thinking how to implement.

[JJP1]

Thanks @Patrick.
So sorry to trouble again.
Now I need to just create job where data is getting compared between source and Target based on load date column.and load into target table only if it is not available in target SAS table
How can I create job like this. Please help.
Can I make use of sql join transformation or how please. Iam currently not getting any idea
Please help

[Patrick · Accepted solution]

@JJP1 

You again don't provide sufficient information to really understand what you have and what you need. 

My answer based on the assumption that:

1. The Date column comes from source and is rather the cutoff date from source (data date) than the load date

2. The Date column is part of the business key (can be used as part of the primary key)

3. You only ever want to load a row into target if there isn't already a row with a matching business key

If so then setting up the table loader as below should work. In below example the business key is {data_date, key_var}

[JJP1]

Sorry @Patrick .please find below.

data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;

data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 89 18NOV2019 19NOV2019
;
run;

incorrect data is there in source dataset for date 18NOV2019
which got alreday loaded into target table.
so now the souce data will become as below.;

data source;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;

So target data should show as below output when i run below code;

data target;
input num $1. amt sourcedate $10. loaddate $10.;
datalines;
1 32 19NOV2019 20NOV2019
2 45 19NOV2019 20NOV2019
3 54 18NOV2019 19NOV2019
;
run;

I would want to do this proc sql option(Sql join transformation0.kindly help