DATA Step, Macro, Functions and more

Proc compare (One SAS Dataset against 10 different SQL tables)

Reply
Frequent Contributor
Posts: 75

Proc compare (One SAS Dataset against 10 different SQL tables)

Hello All,

 

I have a requirement of proc compare of one SAS dataset against 10 sql tables. This SAS dataset has a total of 64,323,006 records which is copied in 10 SQL tables. Below is the SAS dataset breakup inserted into SQL physical tables.

 


Iteration,StartObs,EndObs,TotalObs
1,1,7147000,7147000
2,7147001,14294000,7147000
3,14294001,21441000,7147000
4,21441001,28588000,7147000
5,28588001,35735000,7147000
6,35735001,42882000,7147000
7,42882001,50029000,7147000
8,50029001,57176000,7147000
9,57176001,64323000,7147000
10,64323001,64323006,6

Next I want to run a proc compare of sas dataset against these 10 sql tables (somehow at proc compare these 10 sql tables should appear as a single dataset. I dont want to download these sql tables into sas dataset but directly compare in as is condition)

 

Request if anyone can help on this.

 

Thanks,

 

Anil

 

 

Super User
Posts: 17,819

Re: Proc compare (One SAS Dataset against 10 different SQL tables)

10 proc compares? 

 

You can use FIRSTOBS and OBS dataset options to limit your records. 

 

Super User
Posts: 6,936

Re: Proc compare (One SAS Dataset against 10 different SQL tables)

Create a view

data sqlview / view=sqlview;
set
  sql.table1
  sql.table2
  ...
  sql.table10
;
run;

and use that in the compare.

If library sql points to a external RDBMS, the compare might produce unintended results because of differing number storage.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 139 views
  • 0 likes
  • 3 in conversation