BookmarkSubscribeRSS Feed
buechler66
Barite | Level 11

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.
8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

buechler66
Barite | Level 11
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. 😞
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

buechler66
Barite | Level 11

The table Compare_Error looks like this (see attached).

 

 


Capture.PNG
Kurt_Bremser
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Kurt_Bremser
Super User

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

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1517 views
  • 0 likes
  • 3 in conversation