BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

Hi All,

 

    I am using SAS grid server at Linux. I want to test the tables on different servers (Teradata serve or SQL server) to see whether they are the same.  

   Right now I'm using "proc sort"  then "Proc compare",  it takes long time for big tables. I wonder whether there is any other more efficient way for this data compare purpose? Thank you!

 

Jade

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

The only way SAS can compare tables from different database servers using PROC COMPARE would be to copy the tables into SAS first. This would explain why it is taking so long. It would be helpful if you could post some sample SAS code for one of your compares.

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

The only way SAS can compare tables from different database servers using PROC COMPARE would be to copy the tables into SAS first. This would explain why it is taking so long. It would be helpful if you could post some sample SAS code for one of your compares.

Patrick
Opal | Level 21

@Jade_SAS

To compare data from different environments your first step will always be to load the data into a common environment. In doing so there will always be some sort of data conversion and the only thing you can really compare is the data after conversion (=do I get the same data in my target environment from both sources).

 

If you plan to use the data later on in SAS then loading into SAS for comparison feels like the right thing to do. If you want to speed up elapsed time then consider to implement 3 jobs: Job 1 and 2 load the data from source into SAS and can run in parallel, job 3 doing the comparison depends on job 1&2 and can execute once both tables exist in SAS.

 

If using Proc Compare then make sure to either use the FUZZ= argument (i.e. fuzz=0.00000001) or to use the round() function on all numerical variables with fractional values when loading the data into SAS tables.

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVer...

 

 

Jade_SAS
Pyrite | Level 9

Thank you SASKiwi and Patrick!

If I want to add one filter on one SQL table, is there a way in SAS that I can let the query execute in SQL server (the original table server) instead of in SAS? According to your post, this seems not possible, right?

Jade_SAS
Pyrite | Level 9

Another question is how can we get the rows in table A but not in Table B (or vice versa) when using Proc Compare? thank you!

 

Jade

Patrick
Opal | Level 21

@Jade_SAS

If you want to filter the data before the comparison then simply add this filter condition to the SQL which reads the source table.

If you want to execute the comparison in SQL Server then you need the data in SQL server and you need to use code which executes fully in SQL Server (=out of SAS: Explicit Pass-through SQL).

Proc Compare is a SAS Procedure and it needs the data in SAS. You can use a libname statement pointing to your SQL Server tables and then use Proc Compare but this will still pull all the data into SAS before executing the comparison.

Jade_SAS
Pyrite | Level 9

Thank you so much!

Jade

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1327 views
  • 3 likes
  • 3 in conversation