topic Re: Using MODIFY for updating master dataset from transaction dataset in SAS Programming

Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Thu, 13 Dec 2018 21:59:48 GMT

Hello,

I am trying to clean up some code for processing updates from a transaction dataset to a master dataset. I ran across this SAS paper and subsequent SAS documentation that explains how to use the MODIFY statement to achieve this.

http://www2.sas.com/proceedings/sugi31/264-31.pdf

All of this works as instructed, but I need it to work a little bit differently and I have not been able to figure out how to do it. In this paper, it talks about replacing the record in the master dataset if it also exists in the transaction dataset. The issue I have is that I only want it to replace a record if the data for one of the records variables has changed. In this case above, the entire record is rewritten because they match, regardless if any of the data has changed.

The reason I am trying to do this is because I want to be able to track and report on records that have had data change each day. Is there a way to do this using the methods in this paper or similar?

Thanks!

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Thu, 13 Dec 2018 22:18:27 GMT

The issue I have is that I only want it to replace a record if the data for one of the records variables has changed. In this case above, the entire record is rewritten because they match, regardless if any of the data has changed.

When you say one of the record fields are you referring to a specific field or just if any are updated?

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Thu, 13 Dec 2018 22:21:13 GMT

If any are updated. If nothing in the entire record has changed between the transaction dataset and master, I don't want it to rewrite.

I want to be able to track and report changes but if all records are overwritten that match, I have no way to tell which of those actually had data change.

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Thu, 13 Dec 2018 22:27:26 GMT

How many variables do you have, and what types (num/char) or are you looking for a generic process?

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Thu, 13 Dec 2018 22:34:04 GMT

I have appx 50 variables with a mix of char, num, and dates.

I would like it to be as generic as possible but not sure it can be if I need to be able to tell which specific data was updated.

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Thu, 13 Dec 2018 22:38:56 GMT

@elwayfan446 wrote:

I have appx 50 variables with a mix of char, num, and dates.

I would like it to be as generic as possible but not sure it can be if I need to be able to tell which specific data was updated.

I'm assuming you just want the record that was changed, do you need to know which field was changed?

PROC COMPARE is horrid, but it does give you all this information so it may be a mix of PROC COMPARE to get the changes and then a MODIFY or something that may work the best.

Re: Using MODIFY for updating master dataset from transaction dataset

johnsville — Thu, 13 Dec 2018 22:43:23 GMT

the specific case can be implemented by concatenating all fields together from each record, then comparing the two concatenated strings. update only when the two strings are not equal. The generic case would involve using dataset metadata in such a way as to make the code agnostic, but there would be cases to check for, like when the two datasets are not exactly the same. It would be a lot more code, but could be implemented as a macro - pass that macro the two dataset names, have it identify the equal named variables, then have it create the catx functions for each. If the data sets are required to have exactly the same variables, then have it 'abort cancel' when there is a discrepancy.

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Thu, 13 Dec 2018 22:44:33 GMT

Ideally I would want only the records with differences to be replaced with the modify but then be able to see what fields were changed.

I think at least being able to only get the different records to be replaced that is a good start. I already have a date in each dataset that indicates the date the record was created. If it isn't overwritten in the master data set, I can easily tell which records were updated on any given day because older records dates will not have changed.

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Thu, 13 Dec 2018 22:48:52 GMT

Don't have time to code this up right now, but will later. I would recommend first doing a SQL query between the two tables, using the EXCEPT operator to get only records that have changed. Then use MODIFY to update the data set and get the new dates. That should be relatively easy. Then if you want to know exactly what changed you can run a proc compare between the old and new tables, assuming you keep the old one around for the comparison.

Re: Using MODIFY for updating master dataset from transaction dataset

SASKiwi — Fri, 14 Dec 2018 04:07:28 GMT

This example is a generic way of identifying new or changed rows comparing a master and transaction table using SQL:

data class1;
  set sashelp.class;
run;

data class2;
  set sashelp.class;
  if name = 'Mary' then age = 18;
run;

proc sql;
  create table Changed as
  select distinct name 

  from (
        (select * from class1 
         except corresponding 
         select * from class2 
        )
        union corresponding
        (select * from class2 
         except corresponding 
         select * from class1
        )
       )
  ;
quit;

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Fri, 14 Dec 2018 15:04:23 GMT

Thanks to everyone for responding. I will give some these suggestions a try and report back.

Re: Using MODIFY for updating master dataset from transaction dataset

Tom — Fri, 14 Dec 2018 15:52:47 GMT

Why not just eliminate them from the transactions?

proc sql ;
create table actual_transactions as
  select * from transactions
  except
  select * from master
;
quit;

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Fri, 14 Dec 2018 15:55:16 GMT

Wouldn't this also eliminate any new records in the transactions that need to be added to the master?

Re: Using MODIFY for updating master dataset from transaction dataset

Tom — Fri, 14 Dec 2018 15:59:32 GMT

@elwayfan446 wrote:

Wouldn't this also eliminate any new records in the transactions that need to be added to the master?

How could it possibly do that?

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Fri, 14 Dec 2018 16:03:55 GMT

The more I think about it, the more I understand it. I have never used "except" before.

So if I am thinking correctly, this query will create a dataset that does the following:

1. If the records are in the transactions dataset but not in the master, those records is added to the actual_transaction dataset

2. If the records are in the transactions dataset but any of the variables have changed between that record and the record in the master dataset, then those records are added to the actual_transaction dataset

Am I thinking correctly? If so, then I simply merge or modify that new transaction dataset with the master?

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Fri, 14 Dec 2018 16:06:26 GMT

Its two steps. First remove any actual duplicates using EXCEPT.
Then use MODIFY on the modified transaction data set which now only has the changed records. Then use PROC COMPARE to find the exact differences if you had wanted to.

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Fri, 14 Dec 2018 16:08:40 GMT

@Reeza, will the new transactional dataset also include the new records not in the master?

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Fri, 14 Dec 2018 16:18:08 GMT

Yes, that's the exact purpose of the EXCEPT step. It only removes the exact matches. You will need to test it, for example you may need to exclude the dates from the query since it will have an updated date or something.

Re: Using MODIFY for updating master dataset from transaction dataset

elwayfan446 — Fri, 14 Dec 2018 16:36:54 GMT

Yes, there will be fields that could change in the transactional dataset each day that will need to be updated in the master. If I understand this right, I shouldn't have to write any criteria. Once the exact matches are removed, I am left with everything else... new records and changed records. Once I modify, all of those particular records will be overwritten with any new data for any field that is updated.

I will look up more specifics on proc compare since I have not used it but does it compare on a dataset level or do you have to specify which variables to look at?

Re: Using MODIFY for updating master dataset from transaction dataset

Reeza — Fri, 14 Dec 2018 16:49:38 GMT

@elwayfan446 wrote:

Yes, there will be fields that could change in the transactional dataset each day that will need to be updated in the master. If I understand this right, I shouldn't have to write any criteria. Once the exact matches are removed, I am left with everything else... new records and changed records. Once I modify, all of those particular records will be overwritten with any new data for any field that is updated.

I will look up more specifics on proc compare since I have not used it but does it compare on a dataset level or do you have to specify which variables to look at?

It has the option of either. Note that you will need to save a version of your table before the update so you can compare it.