Best Way To Compare Two CSV Files In SAS

[Cmags]

Hi, all--

I know of Proc Compare, but I do not think it will satisfy my needs...

Here is what I have:

Data Set 1

Data Set 2

I am looking for a way to compare the two tables such that the output table is a result of additions/removals/changes based on Name & Weight...so, if there is the same name on both data sets, did the weight change? Is there a name on the first data set not on the second? Is there a name on the second date set not on the first?

Here is what I'm looking for: 

Rob --> name on both, but weight changed

Ben --> name on first, not on second

Sam --> name on second, not on first

So, essentially, am looking for a table that compiles those three kinds of changes, while showing the value from the first & the second data set if applicable (such as the Rob record).

I have no issues importing the data into SAS, but I am struggling with a way to satisfy the above. Thanks in advance for any help!!

[Cynthia_sas]

Hi:

  Once the data are imported into 2 separate datasets, as you describe, this seems like a RENAME and a MERGE to start with. Something like this, given the 4 rows that you posted for each file (I called them FILE1 and FILE2):

Of course, after being read in, using the same variable names in both files, if you want to merge them to get the final structure you described, then you've got to rename the variables as I show in the MERGE statement.

  This is only getting you the structure you want. You'll have to write additional statements to do the validation and see whether there were changes in the values. The additional datasets created by using the IN= option in the MERGE, just show you how you can get the additional information about who was in one file without being in the other, who was in both files, etc. You could also have just made a "flag" variable instead of doing an output. I like to do the MERGE this way in order to validate the data and catch any really data problems.

Cynthia

[Cmags]

Cynthia, thank you for your help -- this is a great start!

[Kurt_Bremser]

data want;
merge
  ds1 (rename=(color=color1 weight=weight1))
  ds2 (rename=(color=color2 weight=weight2))
;
by name;
if color1 ne color2 or weight1 ne weight2;
run;

[Cmags]

Thanks, Kurt!

This does exactly what I was looking for. I am looking to take this a step further, however. I want to make this a cyclical process...what I mean by this is the following:

I want to continually bring in new lists which will be compared to a 'source of truth'

So, essentially, I want two separate lists

One list that keeps track of all changes (additions/removals/differences in weights) -- I want this to be a running list

One list that is a source of truth

Do you think there's an efficient way to go about this? Thanks!!