@Reeza, will the new transactional dataset also include the new records not in the master?
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?
@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.
Gotcha.
Cool, I will give this a try and let you know how it works. I appreciate all the advice.
@elwayfan446 - The example SQL code I posted above is the basis of how I do updating a master dataset from a transaction dataset. Once you get a dataset containing a list of the rows that have been added or changed, identified by a row key, then you can apply that in the second MODIFY step.
@Reeza... with proc compare, how would I see which records have been added to the master dataset? I can only see the base table, compare table, and a difference record when I do this. Does the modify statement based on the new transactional dataset add new observations to the table that don't currently exist?
@elwayfan446 wrote:
Does the modify statement based on the new transactional dataset add new observations to the table that don't currently exist?
Yes it does, you should test that it gives you what you need with small samples though.
@elwayfan446 wrote:
how would I see which records have been added to the master dataset?
When I run PROC COMPARE with an ID statement that shows the key variables I get this type of output. Not super helpful, but I assume you can also get some of that with the dates you've included as well.
Comparison Results for Observations Observation 1 in WORK.CLASS1 not found in WORK.CLASS2: Name=Alfred. Observation 7 in WORK.CLASS1 not found in WORK.CLASS2: Name=Jane. Observation 7 in WORK.CLASS2 not found in WORK.CLASS1: Name=Jaquie. Observation 17 in WORK.CLASS2 not found in WORK.CLASS1: Name=Sensei.
Here's the code I was using to test:
/*create fake data set*/ data class2; set sashelp.class end=eof; *drop two records; if name in ("Alfred", "Jane") then delete; output; *add two records; if eof then do; name='Jaquie'; age=14; sex='F'; weight=85; height=80; output; name='Sensei'; age=14; sex='M'; weight=90; height=80; output; end; run; *fake data set 2; data class1; set sashelp.class; run; *sort both by name; proc sort data=class1; by name; proc sort data=class2; by name; *compare results; proc compare data=class1 compare=class2 listall; id name; run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.