How do I compare the observations with different Id in the same dataset?

[Ksharp]

No. I mean you need firstly to know what obs belong to BASE , what OBS belong to COMPARE , then proc compare base=BASE compare=COMPARE; run;

[Tom]

The DROP= dataset option takes a list of variables. 12102 is not a valid variable name.

Also you cannot drop the variable that you want to use to subset the data before you have subset the data.  

You could create two separate datasets.  You could trop the Employee_ID from those datasets if you want.

data base compare;
  set sales5;
  if Employee_ID= 12102 then output base;
  if Employee_ID= 12103 then output compare;
  drop employee_id;
run;
  
proc compare base=base compare=compare;
 title 'Comparing Observations that having different id';
run;

[Nancy05]

Hi Tom,

I like your SAS code and tried out your code on my new dataset work.sales6. I have made the data for  Employee_ID 12102  and 12105 identical except for their id.

But the result is not what I expected.:

Comparing Observations that having different id

The COMPARE Procedure                                                                                                      Comparison of WORK.BASE with WORK.COMPARE                                                                                                          (Method=EXACT)                                                                                                                                                                                                                                                          Data Set Summary                                                                                                                                                                                                                                    Dataset                Created          Modified  NVar    NObs                                                                                                                                                                                                            WORK.BASE     13JUL16:11:22:09  13JUL16:11:22:09     8       0                                                                       WORK.COMPARE  13JUL16:11:22:09  13JUL16:11:22:09     8       0                                                                                                                                                                                                                                                                                                                                                                       Variables Summary                                                                                                                                                                                                                                               Number of Variables in Common: 8.                                                                                                                                                                                                                                                                                                                                                                                       Observation Summary                                                                                                                                                                                                                                                                                                                                                                      Number of Observations in Common: 0.                                                                                                 Total Number of Observations Read from WORK.BASE: 0.                                                                                 Total Number of Observations Read from WORK.COMPARE: 0.                                                                                                                                                                                                                   Number of Observations with Some Compared Variables Unequal: 0.                                                                      Number of Observations with All Compared Variables Equal: 0.

Why do they give me the result is 0? Surly I have two identical rows! Here is dataset : work.sales6.

sales6

Obs Employee_ID First_Name Last_Name Gender Salary Job_Title Country Birth_Date Hire_Date 1 120102 Tom Zhou M $108,255 Sales Manager AU AUG1973 01JUN1993 2 120103 Wilson Dawes M $87,975 Sales Manager AU JAN1953 01JAN1978 3 120121 Irenie Elvish F $26,600 Sales Rep. II AU AUG1948 01JAN1978 4 120122 Christina Ngan F $27,475 Sales Rep. II AU JUL1958 01JUL1982 5 120104 Wilson Dawes M $87,975 Sales Manager AU JAN1953 02JAN1978 6 120105 Tom Zhou M $108,255 Sales Manager AU AUG1973 01JUN1993

Nancy

[Tom]

Look higher up in the log. You compared two empty datasets. 

So perhaps the steps to create them is wrong.  Perhaps EMPLOYEE_ID is a character variable instead of a numeric variable? Perhaps it has leading spaces or other non-printing characters that make it so your selection code didn't find the observations you wanted.

[Nancy05 · Accepted solution]

Found the error, missing one zero in Employee_ID, it works well now. Thank you so much for your help! Tom

Here is the ffinal code:

data base compare;
  set work.sales6;
  if Employee_ID= 120102 then output base;
  if Employee_ID= 120105 then output compare;
  drop employee_id;
run;
  
proc compare base=base compare=compare;
 title 'Comparing Observations that having different id';
run;

Nancy

[Reeza]

I assume you have a map of ID1 to ID2?

I would use that as a lookup table to add the new ID to one of the tables. 

Then use proc compare between the two datasets, by that ID that is now the same in both tables. 

You can control the variables that are compared as well. 

[Nancy05]

Hi Reeze,

I am a bit lost at what you are suggesting. Could you take the sample data and provide with SAS code to illustrate what you do mean please?

Thanks,

Nancy

[Reeza]

I didn't see any sample data provided. 

[Tom]

Sounds like you want to use the WHERE= dataset option.

Let's make a sample dataset with and ID variable.

data class;
  id+1;
  set sashelp.class;
run;

Now let's compare the first observations (ID=1) to the second (ID=2).

proc compare
  data=class(where=(id=1))
  compare=class(where=(id=2))
;
run;

[Nancy05]

Hi Tom,

I have tried your code and got errors.

45 proc compare

46 data=class(Where=(id = 1));

47 compare= class(where=(id=2);

_______

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

48 run;

Plus, there is no id variable in sashelp.class.

Nancy

[Tom]

You can't stick extra sem-colons into the middle of a statment and expect SAS to understand it.