DATA Step, Macro, Functions and more

Oracle SQL to SAS Translation

Reply
Regular Contributor
Posts: 212

Oracle SQL to SAS Translation

Hi. I need some help trying to translate this where condition from Oracle SQL to a valid SAS equivelant.  Any help would be greatly appreciated!

 

A.ROWID IN (SELECT TABLE_ROWID FROM IV_RPT.COMPARE_ERROR)

 

The complete Proc SQL I'm trying to run:

proc sql;
create table QueryData400 as         
select 'UNIDENTIFIED DISCREPENCY' as RULE_NM length = 58,
          b.actual_dlvry_date as AD_DT,                 
          b.imb_code length = 31,                 
          400 as RULE_ORDER,
          b.spm_calc_batch_date         
from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b         
where a.imb_code = b.imb_code and intck('day',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45 and ROWID IN (SELECT TABLE_ROWID FROM iv_ora.COMPARE_ERROR);

The error I'm getting:

5   + proc sql;
5   +               create table QueryData400 as         select 'UNIDENTIFIED DISCREPENCY' as RULE_NM length = 58,
    b.actual_dlvry_date as AD_DT,                 b.imb_code length = 31,                 400 as RULE_ORDER,
6   + b.spm_calc_batch_date         from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b         where a.imb_code
= b.imb_code           and intck('day',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45           and ROWID IN (SELECT
TABLE_ROWID
7   + FROM iv_ora.COMPARE_ERROR);
ERROR: The following columns were not found in the contributing tables: ROWID.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
7   +                                 quit;
NOTE: The SAS System stopped processing this step because of errors.
Super User
Super User
Posts: 7,997

Re: Oracle SQL to SAS Translation

Posted in reply to buechler66

Assuming ROWID is a variable, and not an automatic incrementor (its never a good idea to use row position as a comparitor) then you could try something like:

data want;
  set have1 (in=a) have2 (in=b);
  by rowid;
  if a and b;
run;

I.e. if the rowid is in both the base dataset and the IN dataset.  Note that SAS doesn't have that type of in where clause, you need to merge them, or you could do it with hash, but why complicate things?

Regular Contributor
Posts: 212

Re: Oracle SQL to SAS Translation

No, it's not a variable I created. It's an Oracle SQL system variable. I don't have control over the Oracle SQL code, it's just my requirement to replicated it in SAS. I'm stumped. Smiley Sad
Super User
Super User
Posts: 7,997

Re: Oracle SQL to SAS Translation

Posted in reply to buechler66

As I mentioned before, row number automatic variables are not a good idea to code with - if you sort that data you may get a different result.  You could I suppose passthrough the code:

proc sql;
  connect to oracle (path=...);
  select * 
  from  connection to oracle (
    <insert your oracle sql here>
  );
  disconnect from oracle;
quit;

What that does is pass the sql you have from OC, through to the database, so that code natively executes, the result of that query can then be selected from in the outer block.

Super User
Posts: 7,868

Re: Oracle SQL to SAS Translation

Posted in reply to buechler66

There is no column ROWID in either iv_ora.bi_spm_piece_recon or bids_ora.bi_spm_piece_recon.

Inspect the data so you find which column to use.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 212

Re: Oracle SQL to SAS Translation

Posted in reply to buechler66

The table Compare_Error looks like this (see attached).

 

 


Capture.PNG
Super User
Posts: 7,868

Re: Oracle SQL to SAS Translation

Posted in reply to buechler66

buechler66 wrote:

The table Compare_Error looks like this (see attached).

 

 


The relevant tables are the other two. One of them needs to have a column called ROWID for this query to be at least syntactically correct.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,997

Re: Oracle SQL to SAS Translation

Posted in reply to KurtBremser

ROWID is a bit like _n_ in thats implicit in a table.

Super User
Posts: 7,868

Re: Oracle SQL to SAS Translation

[ Edited ]

RW9 wrote:

ROWID is a bit like _n_ in thats implicit in a table.


Ah, I see. Would not use it, but a real identifier instead.

 

Edit:

 

Then the column ROWID should be created when importing the tables into SAS, with the contents of of the Oracle pseudocolumn.

That should then make it safe.

Or, if the whole logic runs in SAS, creating a ROWID initially from _N_ could accomplish the same task.

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