Update, Insert, and Carry Forward Paper

Reply
Frequent Contributor
Posts: 141

Update, Insert, and Carry Forward Paper

Hello everyone,

 

I am trying to create a process that updates a master table from a revision file each day.  I am trying to apply the principles in this paper:

 

http://support.sas.com/resources/papers/proceedings13/417-2013.pdf

 

I am having trouble fulling understanding how to apply the proper keys in order for this to work correctly.  In my data, I need to create a dual primary key for both files, INVESTOR_LOAN_ID and DATE_LOAN_CREATED.  I then need the revision file to update any rows where ANY variable has changed, carry forward (or not touch) anything that hasn't changed, and add new rows to the master file that are now in the revision file. 

 

What is confusing is that the example in the paper lists a keyvar for the index and primary key (this would be my INVESTOR_LOAN_ID and LOAN_CREATE_DATE if I understand it right).  Then it lists a char column for an integrity constraint of not null.  I am trying to determine if only one column was listed here for the example purpose or not.  On all of the other columns I have in my 2 files, I want anything updated on any of those columns, regarldess if it is null or not.  Otherwise, I want the data untouched or carried forward.  Does this mean I can leave these alone in regards to restraints or do I have to do something with each column to make it work.

 

Also, is there a way for it to merge, update, carry forward any records that don't violate the primary key if there is a record that does?  This would be an automated process and I would not want everything to fail because of records that violate it.

 

I just can't fully grasp the article but what it is supposed to do is exactly what I need.

Super User
Posts: 5,376

Re: Update, Insert, and Carry Forward Paper

From the paper:
"if you need to do serious database work and preserve integrity constraints, then you probably should be using SAS Data Integration Studio".
From what I understand from your post it seems you are having trouble understand the bits and bytes. And that's natural. EG is not the tool (nor Base SAS) for building complex data updating patterns, especially when you want to repeat them.
Data never sleeps
Frequent Contributor
Posts: 141

Re: Update, Insert, and Carry Forward Paper

I agree that this might be the desired option but unfortunately that isn't an option I have.  I am trying to determine the best solution with a standard SAS program.

Ask a Question
Discussion stats
  • 2 replies
  • 271 views
  • 0 likes
  • 2 in conversation