05-12-2016 08:58 AM - edited 05-12-2016 09:39 AM
Hi. I thought SAS dates were numbers, but I'm getting an error regardless. Can anyone help me to resolve the issue?
I'm attempting to filter based on these two conditions:
1. imb_code = imb_code AND
2. spm_calc_batch_date's are within 45 days of one another
So even if imb_codes match, but the spm_calc_batch_dates are greater than 45 days apart from one another then it wouldn't be filtered.
5 libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL"; NOTE: Libref IV_ORA was successfully assigned as follows: Engine: ORACLE Physical Name: IVASPRD 6 libname bids_ora oracle user=exfcread pass=XXXXXXXXXXXXXXXXXX path="IBSCRPRD" schema="IMAPSSCR"; NOTE: Libref BIDS_ORA was successfully assigned as follows: Engine: ORACLE Physical Name: IBSCRPRD
145 proc sql; 146 create table QueryData as 147 select subpad('PIECES MISSING IN IV',1,58) as RULE_NM, 148 a.actual_dlvry_date as AD_DT, 149 subpad(a.imb_code,1,31) as IMB_CODE, 150 999.1 as RULE_ORDER, 151 a.spm_calc_batch_date, 152 (a.spm_calc_batch_date - a.spm_calc_batch_date) as spm_calc_batch_date_day_diff 153 from BIDS_ora.bi_spm_piece_recon as a 154 WHERE imb_code not in(select imb_code from IV_ora.bi_spm_piece_recon b where abs(a.spm_calc_batch_date - 154! b.spm_calc_batch_date) <= 45); ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE. SQL statement: SELECT "IMB_CODE", "SPM_CALC_BATCH_DATE" FROM IVPRL.BI_SPM_PIECE_RECON WHERE ( ABS( ( 1766966400 -"SPM_CALC_BATCH_DATE" )) <=TO_DATE(' 01JAN1960:00:00:45','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American') ). NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 155 quit; NOTE: The SAS System stopped processing this step because of errors.
05-12-2016 09:37 AM
Sorry, there are several things wrong with your code. Lets start with the first thing - you state you are trying to join data, but there is no joining in that code? Secondly your diff variable will always be zero, you are subtracting a variable from itself? Thirdly you cannot substract a date from a date, oracle doesn't work that way. Finally, your variables there are datetime values, even if you did manage to subtract one from the other the difference would not be days?
Let me suggest you do this. Firstly get all the data you need from OC = select * from OC and create a SAS dataset from it. Then close OC and forget about that part. Now you have the data in and can work with it. If you want us to provide code to do some mapping from that, you would then need to post on here a datastep which will create some test data - which looks like or is your data, and what you want the output to look like, but I suspect a simple intck('day',45) would be a sufficient bit of code to check a window.
05-13-2016 11:12 AM
Looks like I'm going to use:
WHERE imb_code not in(select imb_code from BIDS_ora.bi_spm_piece_recon b where intck('dtday',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45)
So the complete query would look like this, do you see any potential problems?
proc sql; create table QueryData as select subpad('PIECES MISSING IN BIDS',1,58) as RULE_NM, a.actual_dlvry_date as AD_DT, subpad(a.imb_code,1,31) as IMB_CODE, 999.6 as RULE_ORDER, a.spm_calc_batch_date from IV_ora.bi_spm_piece_recon as a WHERE imb_code not in(select imb_code from BIDS_ora.bi_spm_piece_recon b where intck('dtday',a.spm_calc_batch_date,b.spm_calc_batch_date) <= 45); quit;
05-13-2016 12:08 PM
It might be fine, you will have to try it. I would note that for normal pass-through (i.e. connect to, select from), you can't use SAS functions as the whole SQL code within the clause is sent to the database which obviously doesn't have the SAS functions. Your still probably better pulling all data out to a SAS dataset, then processing that.