topic Re: update master table in SAS Programming

update master table

Tal — Fri, 12 May 2017 00:48:42 GMT

hi,

I have a master table which gets updated by daily SAS datasets.

a) if the record from the daily table is new then simply append it to the master table

b) if the record exists(no changes) in the master then do nothing to it

c)if the clientt exists in the master but he has some updates in the daily file then append his new reocrd to the master and also update the Date field of his previous record with the current date.

In other words after the appending only clients whose two last records in the master table have missing date need to be updated(the Date field of his second last record)

I have attached samles of how table need to look like

Tried with last.id and first.id but got stuck. Any hints please?

Thx

Re: update master table

Patrick — Fri, 12 May 2017 01:31:44 GMT

@Tal

Looks like a slowly changing dimension type 2 (SCD2) to me. If so then I'd have the following columns in the table:

valid_from_dt (that's your current date column)

valid_to_dt (set to something far in the future if current record, else date when expired and new current record added)

change_current_ind (1 if current record, else 0)

Primary key of table: {ID, valid_from_dt}

If you've got SAS DI Studio then use transformation SCD Type 2. This one does the job for you.

Without SAS DI Studio:

1. SQL to determine in your daily increment table which ID's are brand new (inserts) and which one are a change (Update, Insert)

2. Update of all records in master where ID with a change (= valid_to_dt set to date of your increment table, change_current_ind set to 0)

3. Insert of all new and changed record from increment table

What about deletes? Does that ever happen? If so then you'd need another delta file with the deletes (just ID's) used to expire records in your master (= update of valid_to_dt)

If your transaction data set got full volumes then a delete would be all ID's which exist in Master but don't exist in transaction DS.

Re: update master table

Tal — Fri, 12 May 2017 02:09:31 GMT

Thx Patrick
I can get the records (A) from the daily file which are brand new and those with changes. So are you saying i can left join the master with (A) and flag it with "new" and "existing" and if its existing than set the date to current and then simply append (A) to the master?

Re: update master table

Tal — Fri, 12 May 2017 02:11:28 GMT

In fact flag the master with "existing" only and update the date to current and then append (A) to the master table?

Re: update master table

Patrick — Fri, 12 May 2017 06:06:18 GMT

@Tal

So are you saying i can left join the master with (A) and flag it with "new" and "existing" and if its existing than set the date to current and then simply append (A) to the master

From what you've wrote initially you'll also have records in (A) which are identical with master and for these you can't just update the date in master and you need also to exclude these for Insert.

(A) left join Master over ID where Master.Change_Current_Ind=1

- No match: New Record

- Match:

- if difference in one of the other columns under change tracking then Update Record

- if all columns under change tracking identical then "throw away".

- Update Master with (A) where Master.ID=A.ID and Master.Change_Current_Ind=1 and A.<record_type>=<update>

set valid_to_dt to current date (data date of table A)

set change_current_ind=0

- Append (Insert) all records from (A) where A.<record_type> in (<new>,<update>)

valid_from_dt=<data date of table A>

valid_to_dt=<date in far future>

change_current_ind=1

Does that make sense?