topic Re: Oracle SQL to SAS Translation in SAS Programming

Oracle SQL to SAS Translation

buechler66 — Tue, 24 May 2016 12:50:39 GMT

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.

Re: Oracle SQL to SAS Translation

RW9 — Tue, 24 May 2016 12:57:07 GMT

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?

Re: Oracle SQL to SAS Translation

buechler66 — Tue, 24 May 2016 13:24:15 GMT

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. 😞

Re: Oracle SQL to SAS Translation

Kurt_Bremser — Tue, 24 May 2016 13:26:23 GMT

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.

Re: Oracle SQL to SAS Translation

buechler66 — Tue, 24 May 2016 13:26:43 GMT

The table Compare_Error looks like this (see attached).

Re: Oracle SQL to SAS Translation

RW9 — Tue, 24 May 2016 13:32:34 GMT

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.

Re: Oracle SQL to SAS Translation

Kurt_Bremser — Tue, 24 May 2016 13:32:43 GMT

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

Re: Oracle SQL to SAS Translation

RW9 — Tue, 24 May 2016 13:34:41 GMT

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

Re: Oracle SQL to SAS Translation

Kurt_Bremser — Tue, 24 May 2016 13:45:05 GMT

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