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
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.
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.
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.
@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?
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.