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.
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?
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.
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.
The table Compare_Error looks like this (see attached).
@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.
ROWID is a bit like _n_ in thats implicit in a table.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.