About buechler66

buechler66 · ‎05-13-2016

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;

buechler66 · ‎05-13-2016

Hi. I'm wondering if you have suggestions for ways to monitor the progress of a Rsubmit SAS Connect Program. Once I submit my program all I see is: NOTE: Remote submit to ISASPROD commencing. A good deal of my program is a looping macro that creates a dataset named QueryData and appends it to FinalData. This looping can happen up to 100+ times. Depending on server resources my program can take anywhere from 20 minutes or 8+ hours. I'd like to be able to check in and see how far along the program is. Any suggestions?

buechler66 · ‎05-12-2016

What is OC?

buechler66 · ‎05-12-2016

Sorry, I meant Filter Out, not Merge.

buechler66 · ‎05-12-2016

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.

buechler66 · ‎05-10-2016

Hi. Until today I had been told both imb_code and spm_calc_batch_date would be match between both my datasets, so the below Merge made sense. However now the requirement has been changed and I'm honestly stumped. Imb_code will match imb_code, but now the IV dataset's version of spm_calc_batch_date can differ to within 45 days of the BIDS's dataset version spm_calc_batch_date. This is because imb_codes get recycled after 45 days and my dataset contains over 90 days of data. So the bottom line is I need to merge by imb_code and spm_calc_batch_date (within a 45 day window). Is this even possible? Any help would be greatly appreciated! * BIDS Version ; proc sort data=bids_ora.bi_spm_piece_recon out=BIDS_bi_spm_piece_recon; by imb_code spm_calc_batch_date; run; *IV Version ; proc sort data=iv_ora.bi_spm_piece_recon out=IV_bi_spm_piece_recon; by imb_code spm_calc_batch_date; run; * RULE: PIECES MISSING IN IV QUERY ; data QueryData; merge BIDS_bi_spm_piece_recon (in=a) IV_bi_spm_piece_recon (in=b); by imb_code spm_calc_batch_date; if a and not b; run;

buechler66 · ‎05-09-2016

Thanks very much.

buechler66 · ‎05-09-2016

Hi. Can I use a dataset Where condition during a Merge? And if so, how can I change 'is not null' to reflect SAS missing values? Any help would be greatly appreciated. data QueryData; merge bids_ora.bi_spm_piece_recon (in=a) iv_ora.bi_spm_piece_recon (in=b) (where excl_sts_code is not null); by imb_code spm_calc_batch_date; if a and b; run;

buechler66 · ‎05-06-2016

Thanks so much.

buechler66 · ‎05-06-2016

I wonder then what would happen if I instead just Set the datasets together rather then Proc Append them.

buechler66 · ‎05-06-2016

Thanks for the details. Much appreciated. But It's still confusing where the 124 comes from since imb_code is only defined as 31 in one table and as 93 in the other.

buechler66 · ‎05-06-2016

So strange (see attachment). Why does it do this? Especially STR2.

buechler66 · ‎05-06-2016

Hi. I have two datasets of similar content. One dataset has imb_code defined in Oracle as 31 char, the second dataset has imb_code defined as 93 char (but no instances longer than 31). When I Proc Append these datasets I get the message that BASE is 31 and DATA is 124. Where is this 124 coming from? Clearly it's 31+93, but why is this failing my Proc Append? 702 proc append base=FinalData data=QueryData; 703 run; NOTE: Appending WORK.QUERYDATA to WORK.FINALDATA. WARNING: Variable IMB_CODE has different lengths on BASE and DATA files (BASE 31 DATA 124). ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files. NOTE: 0 observations added.

buechler66 · ‎05-06-2016

Hi. I'm wondering if this is the best way to code a join based on the requirement that multiple variables must match in my Where condition? In my original code imb_code was unique for each record, but now I've found that both spm_calc_batch_date and imb_code make a record unique so I need to update the query. select rpad('PIECES MISSING IN IV',300) as RULE_NM, actual_dlvry_date as AD_DT, rpad(imb_code,31) as IMB_CODE, 999.1 as RULE_ORDER, spm_calc_batch_date from ivprl.bi_spm_piece_bids_recon where imb_code||trunc(spm_calc_batch_date) not in(select imb_code||trunc(spm_calc_batch_date) from ivprl.bi_spm_piece_iv_recon)

buechler66 · ‎05-05-2016

The first block of code is working with Length= statements, but the second block is failing an Oracle Prepare Error. Any suggestions? 1506 proc sql; 1507 create table QueryData as 1508 ( select DISTINCT %str(%')&analysis_desc.%str(%') as RULE_NM length = 1200, 1509 b.actual_dlvry_date as AD_DT, 1510 b.imb_code length = 100, 1511 &rule_order as RULE_ORDER, 1512 b.spm_calc_batch_date 1513 from iv_ora.bi_spm_piece_recon a, bids_ora.bi_spm_piece_recon b 1514 where a.spm_calc_batch_date = b.spm_calc_batch_date 1515 and a.imb_code = b.imb_code 1516 and &rule 1517 ); 1518 quit; 1550 proc sql; 1551 connect to oracle as db 1551! (user=&orauser password=&orapass path="ivasprd"); 1552 create table QueryData as 1553 select * from connection to db 1554 ( select 'PIECES MISSING IN IV' as RULE_NM length = 1200, 1555 actual_dlvry_date as AD_DT, 1556 imb_code length = 100, 1557 999.1 as RULE_ORDER, 1558 spm_calc_batch_date 1559 from ivprl.bi_spm_piece_bids_recon 1560 where imb_code||trunc(spm_calc_batch_date) not in(select imb_code||trunc(spm_calc_batch_date) from 1560! ivprl.bi_spm_piece_iv_recon) 1561 ); ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: select 'PIECES MISSING IN IV' as RULE_NM length = 1200, actual_dlvry_date as AD_DT, imb_code length = 100, 999.1 as RULE_ORDER, spm_calc_batch_date from ivprl.bi_spm_piece_bids_recon where imb_code||trunc(spm_calc_batch_date) not in(select imb_code||trunc(spm_calc_batch_date) from ivprl.bi_spm_piece_iv_recon). NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 1562 disconnect from db; NOTE: Statement not executed due to NOEXEC option. 1563 quit; NOTE: The SAS System stopped processing this step because of errors.

Online Status	Offline
Date Last Visited	‎07-24-2020 08:25 PM

Re: Multiple Dataset Options

Multiple Dataset Options

ODS Proc Report Output Using DEFINE AREA / GROUP

Re: ODS Output to Excel - Naming the Excel Tab

ODS Output to Excel - Naming the Excel Tab

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

SAS ODS Excel Output - Proc Report - Word Wrapping

Re: Enlarging a character variable's format length

Re: Enlarging a character variable's format length

Re: Multiple Dataset Options

Re: ODS Proc Report Output Using DEFINE AREA / GROUP

Re: ODS Output to Excel - Naming the Excel Tab

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

Re: Enlarging a character variable's format length

Re: SAS EG and the Output Window - Formatted Variable Output

Re: Looping and defining macro variable

Re: SAS Efficiency Help Needed

Re: RE: PROC TRANSPOSE with correct columns order

Re: ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes

SAS Connect - Monitoring Rsubmit program progress

Re: ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes

Re: ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes

ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes

Big Merge Troubles

Re: Merge with Where condition?

Merge with Where condition?

Re: Proc Append Failure

Re: Proc Append Failure

Re: Proc Append Failure

Re: Proc Append Failure

Proc Append Failure

Where Condition

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA f...