topic Re: How do I compare the observations with different Id in the same dataset? in SAS Programming

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

Nancy05 — Mon, 11 Jul 2016 04:20:39 GMT

Hi,

I need to compare two observations value with different ID. In order to simply the problem, I have created a set of test data as following:

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

I want to compare Employee_ID 120102 with 120105; (or 120103 vs 120104). Is that possible to use proc compare as following?

proc compare base=work.sales5.120103 compare=work.sales5.1201204;
 id Employee_ID;
 title 'Comparing Observations that having different id';
run;

What is correct SAS code?

Please help?

Thanks!

Nancy

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

RahulG — Mon, 11 Jul 2016 05:08:42 GMT

Proc compare is to compare two different dataset and you need comparison between two observation in a dataset.

In your scenario, you need to identify another column that can identify a row. In your below example, it could be FirstName, Last Name , Date of Birth, country.

One way is to use proc sort to get duplicates record but you would not be able to find the difference if there are any.

Another way, you would have to sort the data and then compare _n_ and _n_ -1 rows through base some sas coding in data step

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

Reeza — Mon, 11 Jul 2016 05:10:27 GMT

What's the logic? How do you know which records to compare with which records?

Proc Compare won't work for this, it's most likely going to be a SQL step.

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

SASKiwi — Mon, 11 Jul 2016 07:07:00 GMT

If I understand your problem correctly you are trying to identify employees who have more than one employee ID, in other words duplicated employees. Having duplicate people is a very common problem in many organisations' databases.

The starting point is identifying what keys in your data will in combination allow you to reliably and uniquely identify a person. For example birth date alone is not enough as people can have the same birth date. What about birth date and hire date together? Try sorting your data by birth date and hire date. Are there any people in your data that share these two attributes? Note you will need to be careful of incorrect data in your checking.

If that still isn't good enough perhaps use birth date, hire date and salary together. If this now identifies a person uniquely, sort by these three column and create your own data key:

proc sort data = MyData;
  by Birth_Date Hire_Date Salary;
run;

data MyData2;
  set MyData;
  by Birth_Date Hire_Date Salary;
  if first.salary then Employee_ID_New + 1;
run;

You should now be able to use Employee_ID_New as the key for identifying duplicate employees.

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

Ksharp — Mon, 11 Jul 2016 07:19:19 GMT

Yes. you could. But you need to chang that dataset firstly. If there were at most two obs for an individual .

proc sort data=work.sales5 out=work.have dupout=work.compare nodupkey;
 by First_Name Last_Name;
run;

proc compare base=work.have compare=work.compare;
 id First_Name Last_Name;
 title 'Comparing Observations that having different id';
run;

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

Nancy05 — Mon, 11 Jul 2016 22:35:53 GMT

Thanks for the all reply. I probably need to explain the problem further. The real issues are much more complicated:

1. I need to compare two observations in the same dataset with known ID. Each observation have many (up to 100) variable.

2. For these two obersvations, I don't know which variables would be different, so I need write the code to detect the differents in order to find out which variable (column) has a variation in values . (for example : I know I want to compare 120103 vs 120104)

3. Re: Reeze : What's the logic? Well, If I have a base line data stored in Id of 120103, while source system made changes, I need to make a re-run, so that id of 120104 data populated into SAS. Now I need to compare id 120103 with 120104 and detect any variations in variables (column).

4. Re: SASKiwi: . Employee_Id is purly for illustrate purpose, the real data set will have key vairables such as CR_APPL_NO and HIST_SEQ_NO. The real situation is I have more than 90 data sets to compare with. I always know my base-line id and comparison id

5. Re: Ksharp: Your code working very well for this simple sample problem. What happen the real data don't have such obvious variables such as First_Name and Last_Name to compary by? But I know I want compare id 120103 with 12014. Is there anyway to impove this piece of code?

Thanks!

Nancy

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

Ksharp — Tue, 12 Jul 2016 00:43:09 GMT

So How do you know "I want compare id 120103 with 12014." , What is the keywords to make you want to compare id 120103 with 12014?

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

Nancy05 — Tue, 12 Jul 2016 02:07:23 GMT

Hi Keshan,

The data is a controlled data set, it is my test oracle. The data is injected from source system by running automatic script, so I know the primary key number - application id.

When it is run first time, the application id (eg: id -120103) is automatic created by source system, then the data is extracted, transformed and loaded via some ETL system and finially arrived in SAS. I got base-line data with id (eg: 120103).

Two weeks later, the system has some changes and deployed a new version. Then I run the same automation script second time, the application with new id (eg: 12104) created. In theory, the data should be the same for id 12103 and 12104 except the primary key and its seq No. But I need to prove this has not changed and is populated into SAS correctly.

Is that possible to write code in SAS to make a comparsion for two observations with different id (primary key)?

Thanks!

Nancy

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

Tom — Tue, 12 Jul 2016 02:15:19 GMT

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;

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

Ksharp — Tue, 12 Jul 2016 02:56:45 GMT

So these two tasks are going to generate the similar information for each obs ? The first obs of first task correspond to the first obs of second task ? The second obs of first task correspond to the second obs of second task ? ........ If that so, You can just simply use proc compare + drop PrimaryIndex and ID variable . proc compare base=first(drop=Prim_idx ID) compare=second(drop=Prim_idx ID); run;

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

Nancy05 — Tue, 12 Jul 2016 03:23:55 GMT

Hi Keshan,

Could you provide me with SAS code please? I have tried this but I got errors.

proc compare base=work.sales5(drop=Employee_ID 12103)  compare=work.sales5(drop=Employee_ID 12104) ;
 title 'Comparing Observations that having different id';
run;

Let's take the same sample data I used above.

Thanks!

Nancy

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

Nancy05 — Tue, 12 Jul 2016 03:36:39 GMT

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

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

Reeza — Tue, 12 Jul 2016 03:44:24 GMT

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.

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

Nancy05 — Tue, 12 Jul 2016 03:46:57 GMT

Hi Keshan,

Do you mean something like this?

proc compare base=work.sales5(drop=Employee_ID 12102)  compare=work.sales5(drop=Employee_ID 12103) ;
 title 'Comparing Observations that having different id';
run;

I couldn't get this working. I got some error message. What is the correct syntax?

Thanks,

Nancy

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

Nancy05 — Tue, 12 Jul 2016 03:58:04 GMT

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

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

Ksharp — Tue, 12 Jul 2016 04:33:16 GMT

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;

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

Reeza — Tue, 12 Jul 2016 04:36:45 GMT

I didn't see any sample data provided.

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

Tom — Tue, 12 Jul 2016 18:18:41 GMT

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

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

Tom — Tue, 12 Jul 2016 18:24:24 GMT

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;

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

Nancy05 — Tue, 12 Jul 2016 23:29:22 GMT

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