Compare two Oracle tables data

[japsas100]

Hi,

I would like to compare two Oracle tables in sas and both these tables are present in the different database. I can't have permission to create the link within Oracle to compare these tables.

Is there any other way in sas to compare these tables other than proc compare?

Regards

Japs

[SASKiwi]

Extracting both tables into SAS and comparing them there would be the simplest way - PROC COMPARE is a great way to do this. Since you can't create a link within Oracle then you can't compare them within either Oracle server.

[VDD]

you must have permissions to the Oracle tables.

then you can download the tables and do a SAS compare make sure you enforce some type of a sort order otherwise you may end up with unexpected none compared results that are not what they seem to be.

example if data set 1 has

id code

1 xyz

and data set 2 has

id code

0 xyz

1 xyz

then your SAS compare will fail unless you use a by ID statement.

[ChrisNZ]

If you are allowed to upload to oracle, you can copy a table there via SAS and run the comparison there using SQL, if that's what you really want.

[andreas_lds]

---

@japsas100 wrote:

Hi,

I would like to compare two Oracle tables in sas and both these tables are present in the different database. I can't have permission to create the link within Oracle to compare these tables.

Is there any other way in sas to compare these tables other than proc compare?

Regards

Japs

---

Why not proc compare?

[s_lassen]

One possibility is to do a merge: if you use a by statement in the datastep, SAS tells Oracle to serve data in order.

For instance, if the variables you want to compare are A,B and C:

%let vars = a b c;
%let last_var=%scan(&vars,-1);
data Only1 Only2;
  in1=0; /* by setting these to 0, we catch different number of duplicates */
  in2=0;
  merge 
    oralib1.data_x(keep=&vars in=in1)
    oralib2.data_x(keep=&vars in=in2)
    ;
  by &vars;
  duplicate=not(first.&last_var and last.&last_var);
  if not in1 then output only2;
  else if not in2 then output only1;
run;
  

I put in the initialization of IN1 and IN2, and the check for duplicates, in case there were duplicates in the table.

If both tables have primary keys, that is not necessary if the primary key variables are part of the variables checked.