About buechler66

buechler66 · ‎06-03-2016

So if I understand you correctly it would look like this? proc sql; create table QueryData as select b.actual_dlvry_date as AD_DT, b.imb_code length = 31, b.spm_calc_batch_date from (select * from iv_ora.ods_iv_recon_selected_mp where imb_dlvry_zip_5 = '14221') a inner join (select * from bids_ora.ods_bi_recon_selected_mp where imb_dlvry_zip_5 = '14221') b on a.imb_code = b.imb_code where &rule; quit;

buechler66 · ‎06-03-2016

yah, but there are hundreds of columns so I was hoping for a less manual way of doing this. Any thoughts?

buechler66 · ‎06-03-2016

I would like to merge these two tables. Both tables have the exact same variable names. Is there any way to append a string to the front of each variable name? Like having all variables coming from table aliased as 'A' would have 'A_' as part of the variable name. And columns coming from the table aliased as 'B' would be prefixed with 'B_'. Or is there a built-in way to handle this? I would be doing this so that I could later subset based on variable comparisons rules between like variables. For example: if A_DELIVERY > B_DELIVERY; proc sql; create table MergedData as select a.delivery, b.delivery, a.type_cd, b.type_cd, ...more from iv_ora.ods_iv_recon_selected_mp a, bids_ora.ods_bi_recon_selected_mp b where a.imb_dlvry_zip_5 = '14221' and a.imb_code = b.imb_code; quit;

buechler66 · ‎06-03-2016

Would Views really help with efficiency? Wouldn't Oracle just be running a similar join every time I referenced the View? Both datasets do contain the imb_dlvry_zip_5 column also. Would adding the imb_dlvry_zip_5 to the join speed things up? Like: where a.imb_dlvry_zip_5 = '14221' and a.imb_dlvry_zip_5 = b.imb_dlvry_zip_5 and a.imb_code = b.imb_code AND &rule;

buechler66 · ‎06-03-2016

To clarify, the tables are on two different databases because they are samples of larger tables that originate on two separate databases to begin with. One database is not completely controlled by my company and they will no allow a database link to be placed between the databases. Without a database link I don't believe I can recode this using SQL Pass-thru either.

buechler66 · ‎06-03-2016

The rules look like this... 1 A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL 1.5 A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE 2 NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE) 3 A.LAST_SCAN_DATETIME < B.LAST_SCAN_DATETIME 3.1 A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME 3.2 A.LAST_SCAN_DATETIME IS NOT NULL AND B.LAST_SCAN_DATETIME IS NULL 3.3 A.LAST_SCAN_DATETIME IS NULL AND B.LAST_SCAN_DATETIME IS NOT NULL 3.5 A.ACTUAL_ENTRY_DATETIME IS NULL AND B.ACTUAL_ENTRY_DATETIME IS NOT NULL 4 NVL(A.ACTUAL_ENTRY_DATETIME,SYSDATE) <> NVL(B.ACTUAL_ENTRY_DATETIME,SYSDATE) 6 NVL(A.CRITICAL_ENTRY_TIME,SYSDATE) <> NVL(B.CRITICAL_ENTRY_TIME,SYSDATE) 10 A.INDCTN_MTHD IS NULL AND B.INDCTN_MTHD IS NOT NULL 11 NVL(A.INDCTN_MTHD,-1)<>NVL(B.INDCTN_MTHD,-1) 15 NVL(A.EXPECTED_DLVRY_DATE , SYSDATE ) <> NVL(B.EXPECTED_DLVRY_DATE , SYSDATE ) 19 NVL(A.SVC_STD_ADJ , -1 ) <> NVL(B.SVC_STD_ADJ , -1 ) 20 NVL(A.EPFED_FAC_TYPE_CODE , -1 ) <> NVL(B.EPFED_FAC_TYPE_CODE , -1 ) 21 NVL(A.SVC_VARIANCE , -1 ) <> NVL(B.SVC_VARIANCE , -1 ) 22 NVL(A.ML_CL_CODE , -1 ) <> NVL(B.ML_CL_CODE , -1 ) 25 NVL(A.START_THE_CLOCK_DATE , SYSDATE ) <> NVL(B.START_THE_CLOCK_DATE , SYSDATE ) 26 NVL(A.CLEARANCE_TIME ,'T') <> NVL(B.CLEARANCE_TIME ,'T') 27 NVL(A.STOP_THE_CLOCK_DATE , SYSDATE ) <> NVL(B.STOP_THE_CLOCK_DATE , SYSDATE ) 28 NVL(A.SVC_STD , -1 ) <> NVL(B.SVC_STD , -1 ) 29 NVL(A.INTER_SCF_EXCL_IND ,'T') <> NVL(B.INTER_SCF_EXCL_IND ,'T') 30 NVL(A.SUPPL_CONTR_EXCL_IND ,'T') <> NVL(B.SUPPL_CONTR_EXCL_IND ,'T') 34 NVL(A.SCHD_SHIP_DATE_EXCL_IND ,'T') <> NVL(B.SCHD_SHIP_DATE_EXCL_IND ,'T') 37 NVL(A.SATURDAY_HOLD_IND ,'T') <> NVL(B.SATURDAY_HOLD_IND ,'T') 38 NVL(A.ORGN_FAC_ZIP_5,'T') <> NVL(B.ORGN_FAC_ZIP_5,'T') 39 NVL(A.NON_UNIQ_IMCB_EXCL_IND ,'T') <> NVL(B.NON_UNIQ_IMCB_EXCL_IND ,'T') 40 NVL(A.STOP_SCAN_BEF_AET_EXCL_IND ,'T') <> NVL(B.STOP_SCAN_BEF_AET_EXCL_IND ,'T') 41 NVL(A.CONTR_IRREG_EXCL_IND ,'T') <> NVL(B.CONTR_IRREG_EXCL_IND ,'T') 42 NVL(A.NON_UNIQ_IMTB_EXCL_IND,'T') <> NVL(B.NON_UNIQ_IMTB_EXCL_IND,'T') 43 NVL(A.BMEU_PC_SCAN_BEF_AET_EXCL_IND ,'T') <> NVL(B.BMEU_PC_SCAN_BEF_AET_EXCL_IND ,'T') 44 NVL(A.NON_UNIQ_IMB_EXCL_IND ,'T') <> NVL(B.NON_UNIQ_IMB_EXCL_IND ,'T') 45 NVL(A.UNKNOWN_FAC_EXCL_IND ,'T') <> NVL(B.UNKNOWN_FAC_EXCL_IND ,'T') 48 NVL(A.LONG_HAUL_EXCL_IND,'T') <> NVL(B.LONG_HAUL_EXCL_IND,'T') 51 NVL(A.IMB_CODE ,'T') <> NVL(B.IMB_CODE ,'T') 52 NVL(A.DFLT_IMTB_EXCL_IND ,'T') <> NVL(B.DFLT_IMTB_EXCL_IND ,'T') 53 NVL(A.ML_DRCTN_EXCL_IND ,'T') <> NVL(B.ML_DRCTN_EXCL_IND ,'T') 54 NVL(A.INVLD_CTR_LVL_ENT_FAC_EXCL_IND ,'T') <> NVL(B.INVLD_CTR_LVL_ENT_FAC_EXCL_IND ,'T') 56 NVL(A.ACS_NIXIE_EXCL_IND ,'T') <> NVL(B.ACS_NIXIE_EXCL_IND ,'T') 57 NVL(A.PBV_EXCL_IND ,'T') <> NVL(B.PBV_EXCL_IND ,'T') 58 NVL(A.UNLD_STRT_BEF_ACTL_APPT_IND ,'T') <> NVL(B.UNLD_STRT_BEF_ACTL_APPT_IND ,'T') 60 NVL(A.LAST_SCAN_DV_EXCL_IND ,'T') <> NVL(B.LAST_SCAN_DV_EXCL_IND ,'T') 61 NVL(A.ACS_COA_EXCL_IND ,'T') <> NVL(B.ACS_COA_EXCL_IND ,'T') 62 NVL(A.INVALID_ORGN_ZIP3_EXCL_IND ,'T') <> NVL(B.INVALID_ORGN_ZIP3_EXCL_IND ,'T') 63 NVL(A.IMB_DLVRY_ZIP_3 ,'T') <> NVL(B.IMB_DLVRY_ZIP_3 ,'T') 65 NVL(A.NON_MTCHG_SCAN_APPT_EXCL_IND ,'T') <> NVL(B.NON_MTCHG_SCAN_APPT_EXCL_IND ,'T') 67 NVL(A.NO_SVC_STD_EXCL_IND ,'T') <> NVL(B.NO_SVC_STD_EXCL_IND ,'T') 68 NVL(A.INVALID_ENTRY_FAC_EXCL_IND,'T')<>NVL(B.INVALID_ENTRY_FAC_EXCL_IND,'T') 69 NVL(A.INVLD_FNL_PRCS_ZIP5_EXCL_IND ,'T') <> NVL(B.INVLD_FNL_PRCS_ZIP5_EXCL_IND ,'T') 74 NVL(A.NULL_STC_DV_EXCL_IND ,'T') <> NVL(B.NULL_STC_DV_EXCL_IND ,'T') 77 NVL(A.PW_RGE_CNT_EXCL_IND ,'T') <> NVL(B.PW_RGE_CNT_EXCL_IND ,'T') 78 NVL(A.HIGH_DLVRY_EXCL_IND ,'T') <> NVL(B.HIGH_DLVRY_EXCL_IND ,'T') 79 NVL(A.ML_CAT_CODE , -1 ) <> NVL(B.ML_CAT_CODE , -1 ) 80 NVL(A.STOP_SCAN_BEF_STC_EXCL_IND ,'T') <> NVL(B.STOP_SCAN_BEF_STC_EXCL_IND ,'T') 81 NVL(A.INVALID_DPV_EXCL_IND ,'T') <> NVL(B.INVALID_DPV_EXCL_IND ,'T') 82 NVL(A.IMB_DLVRY_ZIP_3_EXCL_IND ,'T') <> NVL(B.IMB_DLVRY_ZIP_3_EXCL_IND ,'T') 86 NVL(A.STOP_SCAN_DATETIME , SYSDATE ) <> NVL(B.STOP_SCAN_DATETIME , SYSDATE ) 87 NVL(A.NON_MATCH_APPT_EXCL_IND ,'T') <> NVL(B.NON_MATCH_APPT_EXCL_IND ,'T') 88 NVL(A.STOP_THE_CLOCK_EXCL_IND ,'T') <> NVL(B.STOP_THE_CLOCK_EXCL_IND ,'T') 89 NVL(A.ORPHAN_HU_NOT_BMEU_EXCL_IND ,'T') <> NVL(B.ORPHAN_HU_NOT_BMEU_EXCL_IND ,'T') 91 NVL(A.PARS_EXCL_IND ,'T') <> NVL(B.PARS_EXCL_IND ,'T') 92 NVL(A.SV_DUP_CR_EXCL_IND ,'T') <> NVL(B.SV_DUP_CR_EXCL_IND ,'T') 94 NVL(A.FCM_DESTN_EXCL_IND ,'T') <> NVL(B.FCM_DESTN_EXCL_IND ,'T') 95 NVL(A.PC_SCN_BEF_UNLD_SCAN_EXCL_IND ,'T') <> NVL(B.PC_SCN_BEF_UNLD_SCAN_EXCL_IND ,'T') 96 NVL(A.NON_MTCHG_DLVRY_PT_EXCL_IND ,'T') <> NVL(B.NON_MTCHG_DLVRY_PT_EXCL_IND ,'T') 98 NVL(A.MPE_NON_MATCH_ZIP_EXCL_IND ,'T') <> NVL(B.MPE_NON_MATCH_ZIP_EXCL_IND ,'T') 99 NVL(A.INVALID_ORGN_ZIP5_EXCL_IND ,'T') <> NVL(B.INVALID_ORGN_ZIP5_EXCL_IND ,'T') 102 NVL(A.FS_IMD_NESTING_EXCL_IND ,'T') <> NVL(B.FS_IMD_NESTING_EXCL_IND ,'T') 104 NVL(A.APPT_IRREG_EXCL_IND ,'T') <> NVL(B.APPT_IRREG_EXCL_IND ,'T') 105 NVL(A.EPFED_UPDT_EXCL_IND ,'T') <> NVL(B.EPFED_UPDT_EXCL_IND ,'T') 107 NVL(A.INVALID_STC_DATE_EXCL_IND ,'T') <> NVL(B.INVALID_STC_DATE_EXCL_IND ,'T') 108 NVL(A.OTHER_EXCL_IND ,'T') <> NVL(B.OTHER_EXCL_IND ,'T') 110 A.NON_MATCH_FAC_EXCL_IND <> B.NON_MATCH_FAC_EXCL_IND 113 NVL(A.START_THE_CLOCK_EXCL_IND,'T') <> NVL(B.START_THE_CLOCK_EXCL_IND,'T') 199 NVL(A.EXCL_STS_CODE,-7) <> NVL(B.EXCL_STS_CODE,-7) 200 NVL(A.CERTIFIED_MAILER_IND ,'T') <> NVL(B.CERTIFIED_MAILER_IND ,'T') 300 NVL(A.FIRST_SCAN_DATETIME,SYSDATE) <> NVL(B.FIRST_SCAN_DATETIME,SYSDATE) 301 NVL(A.SV_SCAN_IND,'T') <> NVL(B.SV_SCAN_IND,'T') 302 NVL(A.MAILG_DATE , SYSDATE ) <> NVL(B.MAILG_DATE , SYSDATE ) 304 NVL(A.PLANT_DU_FAIL_RSN_CODE ,'T') <> NVL(B.PLANT_DU_FAIL_RSN_CODE ,'T') 305 NVL(A.LAST_SCAN_OP_CODE ,'T') <> NVL(B.LAST_SCAN_OP_CODE ,'T') 306 NVL(A.PART_ONE_DAY_CNT , -1 ) <> NVL(B.PART_ONE_DAY_CNT , -1 ) 307 NVL(A.FEDEX_AIR_SCAN_IND ,'T') <> NVL(B.FEDEX_AIR_SCAN_IND ,'T') 308 NVL(A.FIRST_SCAN_MACH_TYPE ,'T') <> NVL(B.FIRST_SCAN_MACH_TYPE ,'T') 309 NVL(A.RECORD_TYPE_CODE , -1 ) <> NVL(B.RECORD_TYPE_CODE , -1 ) 310 NVL(A.LPO_TYPE_CODE , -1 ) <> NVL(B.LPO_TYPE_CODE , -1 ) 311 NVL(A.LAST_OFD_SCAN_DATETIME , SYSDATE ) <> NVL(B.LAST_OFD_SCAN_DATETIME , SYSDATE ) 312 NVL(A.END_TO_END_TRANSIT_HRS_ADJ , -1 ) <> NVL(B.END_TO_END_TRANSIT_HRS_ADJ , -1 ) 313 NVL(A.STOP_SCAN_IND ,'T') <> NVL(B.STOP_SCAN_IND ,'T') 314 NVL(A.STOP_SCAN_OP_CODE ,'T') <> NVL(B.STOP_SCAN_OP_CODE ,'T') 315 NVL(A.RATE_TYPE_CODE , -1 ) <> NVL(B.RATE_TYPE_CODE , -1 ) 316 NVL(A.THS_CLEAN_AIR_SCAN_IND ,'T') <> NVL(B.THS_CLEAN_AIR_SCAN_IND ,'T') 317 NVL(A.CAIR_SCAN_IND ,'T') <> NVL(B.CAIR_SCAN_IND ,'T') 318 NVL(A.STOP_SCAN_FAC_ZIP_CODE ,'T') <> NVL(B.STOP_SCAN_FAC_ZIP_CODE ,'T') 319 NVL(A.END_TO_END_TRANSIT_HRS , -1 ) <> NVL(B.END_TO_END_TRANSIT_HRS , -1 ) 320 NVL(A.IMB_DLVRY_ZIP_7 ,'T') <> NVL(B.IMB_DLVRY_ZIP_7 ,'T') 321 NVL(A.LAST_SCAN_FAC_ZIP_CODE ,'T') <> NVL(B.LAST_SCAN_FAC_ZIP_CODE ,'T') 322 NVL(A.PRDCL_COUNTY_IND ,'T') <> NVL(B.PRDCL_COUNTY_IND ,'T') 323 NVL(A.IMB_DLVRY_ZIP_5 ,'T') <> NVL(B.IMB_DLVRY_ZIP_5 ,'T') 324 NVL(A.COMAIL_IND ,'T') <> NVL(B.COMAIL_IND ,'T') 325 NVL(A.RATE_CAT_CODE,-1) <> NVL(B.RATE_CAT_CODE,-1) 326 NVL(A.FIRST_OFD_SCAN_DATETIME,SYSDATE)<>NVL(B.FIRST_OFD_SCAN_DATETIME,SYSDATE) 327 A.HU_LVL_CODE IS NULL AND B.HU_LVL_CODE IS NOT NULL 328 NVL(A.FSS_SCAN_IND ,'T') <> NVL(B.FSS_SCAN_IND ,'T') 329 NVL(A.RANGE_IND ,'T') <> NVL(B.RANGE_IND ,'T') 330 NVL(A.IMTB_CODE ,'T') <> NVL(B.IMTB_CODE ,'T') 331 NVL(A.FIRST_MILE_HOURS , -1 ) <> NVL(B.FIRST_MILE_HOURS , -1 ) 332 NVL(A.LAST_SCAN_MACH_TYPE,-1)<>NVL(B.LAST_SCAN_MACH_TYPE,-1) 333 NVL(A.HU_LVL_CODE,-1)<>NVL(B.HU_LVL_CODE,-1) 334 NVL(A.HU_TYPE_CODE , -1 ) <> NVL(B.HU_TYPE_CODE , -1 ) 335 NVL(A.HU_LBL_CIN_CODE ,'T') <> NVL(B.HU_LBL_CIN_CODE ,'T') 336 NVL(A.CONTR_LVL_CODE,-1) <> NVL(B.CONTR_LVL_CODE,-1) 337 NVL(A.PREP_TYPE_CODE,-1) <> NVL(B.PREP_TYPE_CODE,-1) 113 rows selected.

buechler66 · ‎06-03-2016

I have the following SAS code that references and joins two large Oracle tables via Oracle libnames (~500 million records per table). This query takes hours and repeats with varying &rule conditions, so the whole program runs for over a day. Are there any strategies I can employ to make the query run faster? libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVAS"; libname bids_ora oracle user=exfcread pass="mypw" path="IBSCRPRD" schema="IMAPSSCR"; proc sql; create table QueryData as select b.actual_dlvry_date as AD_DT, b.imb_code length = 31, b.spm_calc_batch_date from iv_ora.ods_iv_recon_selected_mp a, bids_ora.ods_bi_recon_selected_mp b where a.imb_dlvry_zip_5 = '14221' and a.imb_code = b.imb_code AND &rule; quit;

buechler66 · ‎05-26-2016

Hi. I've had to change some code from SQL Pass-Thru to Proc SQL and while the Where conditions haven't changed the number of records being pulled has changed to an incorrect number. SQL Pass-Thru is pulling directly from the Oracle tables where ACTUAL_DLVRY_DATE is defined as a Date type. Whereas the Proc SQL is referencing an Oracle Libname instead. Can anyone identify what is going wrong and how it can be corrected? Any help would be greatly appreciated! This SQL Pass-Thru code pulls the correct number of records at 341. proc sql; connect to oracle as db (user=&orauser password=&orapass path="ivasprd"); create table QueryData as select * from connection to db ( select b.imb_code from ivprl.bi_spm_piece_iv_recon a, ivprl.bi_spm_piece_bids_recon b where a.imb_code = b.imb_code and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL ); disconnect from db; quit; But this Proc SQL code pulls the wrong number of records at 29 libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVPRL"; proc sql; create table QueryData%sysfunc(tranwrd(&rule_order,.,_)) as select b.imb_code length = 31 from iv_ora.bi_spm_piece_iv_recon a, iv_ora.bi_spm_piece_bids_recon b where a.imb_code = b.imb_code and A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL; quit;

buechler66 · ‎05-24-2016

The table Compare_Error looks like this (see attached).

buechler66 · ‎05-24-2016

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

buechler66 · ‎05-24-2016

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.

buechler66 · ‎05-19-2016

Thank you.

buechler66 · ‎05-19-2016

Has anyone encountered an error like this before? I find it rather cryptic. Any possible solutions would be appeciated. 157 + proc sql; 157 + create table QueryData54 as ( select DISTINCT 'INVLD CTR LVL ENT FACILITY EXCL INDICATOR MISMATCH' as RULE_NM length = 58, b.actual_dlvry_date as AD_DT, b.imb_code length = 31, 54 as RULE_ORDER, 158 + b.spm_calc_batch_date from iv_ora.bi_spm_piece_recon a, bids_ora.bi_spm_piece_recon b where a.spm_calc_batch_date = b.spm_calc_batch_date and a.imb_code = b.imb_code and COALESCE NOTE: Line generated by the CALL EXECUTE routine. 159 +(A.ICOALESCED_CTR_LVL_ENT_FAC_EXCL_IND ,'T') <> COALESCE(B.ICOALESCED_CTR_LVL_ENT_FAC_EXCL_IND ,'T') ); ----------------------------------- ----------------------------------- 65 65 NOTE: The "<>" operator is interpreted as "not equals". ERROR 65-58: Name 'ICOALESCED_CTR_LVL_ENT_FAC_EXCL_IND' is too long for a SAS name in this context. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds ERROR: File WORK.QUERYDATA54.DATA does not exist. NOTE: Statements not processed because of errors noted above.

buechler66 · ‎05-17-2016

Hi. I'm looking for a way to insert the datetime at the beginning of my program into my Log and then again at the end of my program. Because I run the program on a shared server I'd like to record how long each run of my program takes. I know already it can vary greatly from day-to-day. Can anyone suggest some code for this? Any help would be appreciated!

buechler66 · ‎05-13-2016

Hmm..how can I write out a filename? Maybe I could write a filename and append on the &rule_order somehow. Like QueryData_&rule_order perhaps? Yah, each iteration is a Rule loop. And each record can only be flagged with the first rule's criteria it meets. And the rules are run in order of precedence from 1 to 100+. So if a record qualifies for Rule 1 then it cannot also qualify for Rule 2 on. * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; %macro BuildQueryData(analysis_desc= , rule= , rule_order= ); proc sql; create table QueryData as ( select DISTINCT %str(%')&analysis_desc.%str(%') as RULE_NM length = 58, b.actual_dlvry_date as AD_DT, b.imb_code length = 31, &rule_order 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.spm_calc_batch_date = b.spm_calc_batch_date and a.imb_code = b.imb_code and &rule ); quit; * Append datasets to final dataset ; proc append base=FinalData data=QueryData force; run; * Create unique index (if not exist), to meet the criteria that only one record (imb) per ; * rule is kept and counted in the data ; data _null_; set sashelp.vmember (where=(Libname='WORK' and memname='FINALDATA')); if upcase(index) = 'NO' then call execute('%CreateUniqueIndex'); RUN; %mend BuildQueryData;

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: Merge of two tables with same column names

Re: Merge of two tables with same column names

Merge of two tables with same column names

Re: SAS Efficiency Help Needed

Re: SAS Efficiency Help Needed

Re: SAS Efficiency Help Needed

SAS Efficiency Help Needed

SQL Pass-Thru vs Proc SQL - Same query has different results

Re: Oracle SQL to SAS Translation

Re: Oracle SQL to SAS Translation

Oracle SQL to SAS Translation

Re: ERROR 65-58: Name 'ICOALESCED_CTR_LVL_ENT_FAC_EXCL_IND' is too lon...

ERROR 65-58: Name 'ICOALESCED_CTR_LVL_ENT_FAC_EXCL_IND' is too long fo...

SAS Program Times

Re: SAS Connect - Monitoring Rsubmit program progress