Compare rows in a dataset

[Anita_n]

 @Tom  @Quentin   @Astounding  @ballardw  Thankyou all for your contribution to find a solution to this problem

After consultation with the customer. Here is a detailed explanation of his intentions with the data. I have adjusted (added the variable that contains the person who entered the data) the data a little.

data have;
input id $ pnr pid time cn editor var1 var2 var3 var4 var5;
datalines;
1 12 2 1 1 1 5 6 7 3 4
1 12 2 1 2 2 5 7 9 3 5
1 12 2 2 2 3 3 8 5 1 5
1 12 2 2 2 4 3 8 7 1 6

15 6 3 1 1 1 3 5 8 3 4
15 6 3 2 2 2 3 6 8 3 5
15 6 3 2 2 3 1 7 5 1 5

25 7 3 1 1 1 3 5 8 3 4
25 7 3 2 2 2 3 6 8 3 5
25 7 3 1 2 3 1 7 5 1 5
25 7 3 1 2 4 3 8 7 1 6

;
run;

a) Identify the matching pairs of the 1st and 2nd input 
       - there should always be 2 observations that match in the variables with match in the id, pnr and pid  
       - these observations differ by the number of the control input in the variable 'control' (1 or 2)

b) Check all data records to see whether they already have a control entry or not; these two groups should be outputted                   separately
      - a group with observations that do not yet have a 2nd input
       - a group with observations for which there is a 2nd input

c) Compare the observations that exist twice with each other, prerequisite as described above with match in id, pnr, pid
     - then compare whether the var1-var5 match (by rows) or not,
      - observations that do not match should be output so that we know what needs to be corrected

So these are the notes I made. I hope someone can give me a helping hand. Even if it's just the start because am little bit confused of where to start from. Thanks

[ballardw]

Editor values of 1,2,3, 4 sure don't look like two people entering data which was part of the prior description.

Please restrict use of "data set" to discussion about SAS data sets. You are apparently talking about rows of values in the text file as a data set and that doesn't make much sense (at least not to me) and confuses where a data set may actually be needed.

You may need to provide a bit more detail about this

b) Check all data records to see whether they already have a control entry or not; these two groups should be outputted                   separately
      - a group with data records that do not yet have a 2nd input
       - a group with data records for which there is a 2nd input

As in which records in your example meet each requirement and it would be nice for consistency sake to provide something like the name of an output data set to hold which "separate" outputs

[Anita_n]

@ballardw  Thanks for your remarks:

Q: Editor values of 1,2,3, 4 sure don't look like two people entering data which was part of the prior description.

A: I did not want to use 1, 2 for that this doesn't confuse with time and control

Q: Please restrict use of "data set" to discussion about SAS data sets. You are apparently talking about rows of values in the text file as a data set and that doesn't make much sense (at least not to me) and confuses where a data set may actually be needed.

A: Sorry I have editted this above

Q: 

You may need to provide a bit more detail about this.

b) Check all data records to see whether they already have a control entry or not; these two groups should be outputted                   separately
      - a group with data records that do not yet have a 2nd input
       - a group with data records for which there is a 2nd input

As in which records in your example meet each requirement and it would be nice for consistency sake to provide something like the name of an output data set to hold which "separate" outputs

A:  Identify the matching pairs of the 1st and 2nd input 
       - there should always be 2 observations that match in the variables with match in the id, pnr, pid and time 

       - The values of id, pnr, pid  should always be the same to become a matching pair. In variable "control" there should be a first and second entry to see that two different people entered the data

     - Like in observation one and two  id=1 , since id, pnr, pid match they are pairs and there exist control 1 and 2 for the values of var1 to var5 could be checked to if if they are the same or not. If they are the same I output them in a dataset  "all_values_match". If they do not match I output them in a data set "values_doesnt_match"

     -Then I check for the next pair with the same id,  pnr, pid but I have only "control"=2 so  I know control one is definitely missing.  I then check if these are duplicates if not I output them in dataset "missing_entry" etc

I hope I could answer your questions

[Tom · Accepted solution]

So trying to pull key information from your post it looks like groups in the data is identified by the values of three variables:  id pnr pid

Each group could appear once or twice. (Are you sure there is never a third or fourth observation in a group?)

So split the data into three files.  The singletons.  The first and the second.

data single first second;
  set have;
  by id pnr pid;
  if first.pid and last.pid then output single;
  else if first.pid then output first;
  else output second;
run;

So the dataset SINGLE is the answer to which ones have not had what we used to call second pass data entry.

You can then PROC COMPARE with the other two datasets to see which records had differences.

[Quentin]

Let's make a simpler example dataset.  The data has one row per PatientID.  The data for each PatienID was entered by two different people (EntryID):

data have;
input ptid entryid var1 var2 var3;
datalines;
1 1 2 4 6
1 2 2 5 6
2 1 10 12 14
2 2 10 12 14
3 1 2 4 6
;
run;

So that is basically your data, but with fewer ID variables.  When asking questions, it's helpful to make the HAVE data as simple as possible.  Anything that works for one ID variable can work for multiple.

With that, I would use PROC COMPARE:

proc compare base=have(where=(entryid=1)) compare=have(where=(entryid=2)) listobs;
  var var1-var3 ;
  id ptid ;
run ;

That will show you where are are differences in the value of var1-var3.  It will also show you when data for a patient was only entered once.  If data for a patient was entered 3 times, you'll get a warning in your log about duplicate data. It will work for numeric data and character data.  You don't even have to list all of the variables.  PROC COMPARE is fabulous!

If you want output data instead of a report, you can explore the options in PROC COMPARE to create output datasets.

[Anita_n]

@Tom @Quentin @ballardw @Astounding  You guys are really great. Thanks a lot for the great job. It worked, after a little changes

[Stephen_Shell]

For those used to executing First. Last. but sometimes feel uncertain if its hard to scan the data by sight. 

My problem was similar to the one posted but I needed to check if there were any errors/differences in obs/rows with 51 variables .

So I did a test with one of SAS's Proc Sort data sets. I'd like a comment if I'm off base.

Data Company; input Company $ 1-22 Debt 25-30 Number 33-36 Town $ 39-51;
datalines;
Ice Cream Delight 299.98 2310 Holly Springs
Ice Cream Delight 299.98 2210 Holly Springs
Ice Cream Delight 299.98 2310 Holly Springs
Ice Cream Delight 300.98 2310 Holly Springs
Ice Cream Delight 300.98 2510 Holly Springs
Ice Cream Delight 300.98 2510 Nolly Springs
Ice Cream Delight 300.98 2510 Nolly Springs
Ice Cream Delight 300.98 2510 Nolly Springs
;

Proc sort data=Company;by Company Debt Number Town ;

Data Company2;
Set Company    ;   by Company Debt Number Town ;
If First.Company=1 then FDC='1';
If First.Company=0 then FDC='0';
If First.Debt=1 then FDd='1';
If First.Debt=0 then FDd='0';
If First.Number=1 then FDn='1';
If First.Number=0 then FDn='0';
If First.Town=1 then FDt='1';
If First.Town=0 then FDt='0';

If First.Company=0 and First.Town=1 then FCFT='01';

Proc print;run;

Obs Company Debt Number Town FDC FDd FDn FDt FCFT12345678

Ice Cream Delight	299.98	2210	Holly Springs	1	1	1	1
Ice Cream Delight	299.98	2310	Holly Springs	0	0	1	1	01
Ice Cream Delight	299.98	2310	Holly Springs	0	0	0	0
Ice Cream Delight	300.98	2310	Holly Springs	0	1	1	1	01
Ice Cream Delight	300.98	2510	Holly Springs	0	0	1	1	01
Ice Cream Delight	300.98	2510	Nolly Springs	0	0	0	1	01
Ice Cream Delight	300.98	2510	Nolly Springs	0	0	0	0
Ice Cream Delight	300.98	2510	Nolly Springs	0	0	0	0

[ballardw]

@Stephen_Shell 

Best practice for this forum is that you start your own post and possibly reference this one as related. Readers wont have to wade through the original posts to find the details of your question and responses to questions about your problem. Also when a solution is reached you can indicate which answer fits your problem.

Also, you want to post code in a code box opened with the </> or "running man" icon on this forum. Pasting code in the main message will get reformatted by the forum software. As posted, your data step reading the data throws errors because column 22, which is supposed to part of the Company from your input statement actually includes the start of the Debt variable and Town starts in column 31 not 39. The forum likely removed repeated blanks. An example from the log running your posted code:

1    Data Company; input Company $ 1-22 Debt 25-30 Number 33-36 Town $
1  ! 39-51;
2    datalines;

NOTE: Invalid data for Number in line 3 33-36.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----
3          Ice Cream Delight 299.98 2310 Holly Springs
Company=Ice Cream Delight 299. Debt=2310 Number=. Town=rings _ERROR_=1
_N_=1
NOTE: Invalid data for Number in line 4 33-36.
4          Ice Cream Delight 299.98 2210 Holly Springs
Company=Ice Cream Delight 299. Debt=2210 Number=. Town=rings _ERROR_=1
_N_=2
NOTE: Invalid data for Number in line 5 33-36.
5          Ice Cream Delight 299.98 2310 Holly Springs
Company=Ice Cream Delight 299. Debt=2310 Number=. Town=rings _ERROR_=1
_N_=3
NOTE: Invalid data for Number in line 6 33-36.
6          Ice Cream Delight 300.98 2310 Holly Springs
Company=Ice Cream Delight 300. Debt=2310 Number=. Town=rings _ERROR_=1
_N_=4
NOTE: Invalid data for Number in line 7 33-36.
7          Ice Cream Delight 300.98 2510 Holly Springs
Company=Ice Cream Delight 300. Debt=2510 Number=. Town=rings _ERROR_=1
_N_=5
NOTE: Invalid data for Number in line 8 33-36.
8          Ice Cream Delight 300.98 2510 Nolly Springs
Company=Ice Cream Delight 300. Debt=2510 Number=. Town=rings _ERROR_=1
_N_=6
NOTE: Invalid data for Number in line 9 33-36.
9          Ice Cream Delight 300.98 2510 Nolly Springs
Company=Ice Cream Delight 300. Debt=2510 Number=. Town=rings _ERROR_=1
_N_=7
NOTE: Invalid data for Number in line 10 33-36.
10         Ice Cream Delight 300.98 2510 Nolly Springs
Company=Ice Cream Delight 300. Debt=2510 Number=. Town=rings _ERROR_=1
_N_=8
NOTE: The data set WORK.COMPANY has 8 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds

Hint: at least for examples on this forum it may be better to use simple input statement with numeric values instead of specifying columns.

Then provide a few more details as to what exactly you need for output.

You say your problem is "similar" to the topic in this thread but are not particularly clear about the differences other than mentioning "51 variables".

There would be no way for us to determine what an "error" might be without rules defining what an error is.

Differences might be easier using Proc Compare which is designed for comparing two data sets (or one set with itself).

I would submit that a variable such as "debt" (or price, balance, number in inventory etc) is very likely to be time dependent and comparisons likely should be compared based on some date measure.

If you are looking for potential differences in Number (an address component? store indentification? descriptions help) and City where you expect only one value of Number per City for a given country a diagnostic tool that may be helpful is proc freq with the list option:

Consider:

Proc freq data=company;
   table company *town *number/list;
run;

The company data set would not require sorting though if very large sorting might improve the run time for the proc freq.

The LIST option gives a count of the combinations of the variables on a single line and you can tell how many values of NUMBER you get for each combination of Company and Town easily. 

You could also inlcude

   table company * number * town / list;

Which would clearly show if number was associated with multiple towns.

I had a data source that had, and required reporting on by, city, county and Zipcode. However the data entry folks were more than a bit sloppy and would enter the county in the city field, or the State in the County field. The state name was also the name of one of the counties and several county names were the same as cities, a not uncommon occurence. So I used this technique to find counties not associated with the city name and vice versa. And the Zipcode because those would often have reversed digits or other inconsistencies. This step using proc freq was after use the SASHELP.ZIPCODE data set to identify likely errors involving Zipcodes. Caution: Zipcodes may cross county lines and frequently do in rural areas.