05-24-2016 08:50 AM
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.
05-24-2016 08:57 AM
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?
05-24-2016 09:24 AM
05-24-2016 09:32 AM
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.
05-24-2016 09:26 AM
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.
05-24-2016 09:32 AM
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.
05-24-2016 09:40 AM - edited 05-24-2016 09:45 AM
ROWID is a bit like _n_ in thats implicit in a table.
Ah, I see. Would not use it, but a real identifier instead.
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.