https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746542#M234199 <P>Hi,</P> <P>I have two datafiles which actually have the same set of variables but the set of vars is named differently across the two files. Each person should be found in both files and should (but will not always) have identical sets of values across each variable:</P> <P>File 1:</P> <P>ID_1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e</P> <P>c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; f&nbsp;</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; g</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i&nbsp;&nbsp;</P> <P>File 2:</P> <P>person_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; firstvar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; secondvar</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e</P> <P>c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; f</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; h</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i</P> <P>&nbsp;</P> <P>What I need to do is:</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1. Look across the values of each variable for each person and make sure that the value of the var on File1 matches the value of the var on File2 for that&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; person.&nbsp;</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2. Produce output which gives a list of IDs:</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a) That have at least one set of vars which failed to match</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b) For those IDs, what were the variables that did not match, eg:</P> <P>&nbsp;</P> <P>ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; non-matching vars&nbsp;</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var2, secondvar</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var1, firstvar</P> <P>&nbsp;</P> <P>If an ID has multiple sets of non-matching variables, my preference would be for a new row for each mismatch for a given ID.</P> <P>&nbsp;</P> <P>What is the most efficient way to approach this? Obviously I know PROC COMPARE is built for the comparison part, but the output isn't exactly ideal for what I'm trying to produce.&nbsp;</P> <P>&nbsp;</P> Tue, 08 Jun 2021 18:28:53 GMT Walternate 2021-06-08T18:28:53Z https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746542#M234199 <P>Hi,</P> <P>I have two datafiles which actually have the same set of variables but the set of vars is named differently across the two files. Each person should be found in both files and should (but will not always) have identical sets of values across each variable:</P> <P>File 1:</P> <P>ID_1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e</P> <P>c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; f&nbsp;</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; g</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i&nbsp;&nbsp;</P> <P>File 2:</P> <P>person_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; firstvar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; secondvar</P> <P>a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d</P> <P>b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e</P> <P>c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; f</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; h</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i</P> <P>&nbsp;</P> <P>What I need to do is:</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1. Look across the values of each variable for each person and make sure that the value of the var on File1 matches the value of the var on File2 for that&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; person.&nbsp;</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2. Produce output which gives a list of IDs:</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a) That have at least one set of vars which failed to match</P> <P>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b) For those IDs, what were the variables that did not match, eg:</P> <P>&nbsp;</P> <P>ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; non-matching vars&nbsp;</P> <P>d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var2, secondvar</P> <P>e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var1, firstvar</P> <P>&nbsp;</P> <P>If an ID has multiple sets of non-matching variables, my preference would be for a new row for each mismatch for a given ID.</P> <P>&nbsp;</P> <P>What is the most efficient way to approach this? Obviously I know PROC COMPARE is built for the comparison part, but the output isn't exactly ideal for what I'm trying to produce.&nbsp;</P> <P>&nbsp;</P> Tue, 08 Jun 2021 18:28:53 GMT https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746542#M234199 Walternate 2021-06-08T18:28:53Z https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746596#M234212 <P>Hello,</P> <P>&nbsp;</P> <P>Why can't you use Proc Compare for this?</P> <P>&nbsp;</P> <P>Many thanks,</P> <P><BR />Kriss&nbsp;</P> Tue, 08 Jun 2021 21:43:02 GMT https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746596#M234212 djrisks 2021-06-08T21:43:02Z https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746597#M234213 <P>Perhaps you could also use Proc SQL with the full join, and so you will see the non matching values on different rows...</P> Tue, 08 Jun 2021 21:44:49 GMT https://communities.sas.com/t5/SAS-Programming/Comparison-of-two-identical-sets-of-values-with-output-at-the/m-p/746597#M234213 djrisks 2021-06-08T21:44:49Z