topic Re: Compare two Oracle tables data in SAS Programming

Compare two Oracle tables data

japsas100 — Thu, 21 Mar 2019 00:37:27 GMT

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

Re: Compare two Oracle tables data

SASKiwi — Thu, 21 Mar 2019 00:45:33 GMT

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.

Re: Compare two Oracle tables data

VDD — Thu, 21 Mar 2019 01:18:21 GMT

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.

Re: Compare two Oracle tables data

ChrisNZ — Thu, 21 Mar 2019 02:07:08 GMT

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.

Re: Compare two Oracle tables data

andreas_lds — Thu, 21 Mar 2019 12:06:50 GMT

@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?

Re: Compare two Oracle tables data

s_lassen — Thu, 21 Mar 2019 13:21:18 GMT

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.