OMG!  I just wasn't even looking in that direction.  Thanks so much! ... View more

And with regard to the "#", I've tried it with no quotes, and single and double quotes. Grr.... ... View more

Yes! Thank you. ... View more

I've tried 'separated by' using many different characters.  Doesn't seem to make a difference.   NVL isn't a SAS function, it's an Oracle function. It's just part of the data being stored in the Rule variable. ... View more

Thank you. I appreciate your time. ... View more

Got it!!!   %macro loopy; %local i j zip5 rule zip_list rule_list rule_order rule_order_list analysis_desc analysis_desc_list; proc sql; select trim(zip5) into : zip_list separated by '#' from sampledzips; select trim(rule) into : rule_list separated by '#' from QueryRules; select put(rule_order,3.1) into : rule_order_list separated by '#' from QueryRules; select trim(analysis_desc) into : analysis_desc_list separated by '#' from QueryRules; quit; %do i=1 %to %sysfunc(countw(&zip_list,'#')); %let zip5 = %scan(&zip_list, &i,'#'); %do j=1 %to %sysfunc(countw(&rule_list,'#')); %let rule = %scan(&rule_list, &j,'#'); %let rule_order = %scan(&rule_order_list, &j,'#'); %let analysis_desc = %scan(&analysis_desc_list, &j,'#'); %put VARIABLES: &i &j &zip5 &rule &rule_order &analysis_desc; %end; %end; %mend loopy; %loopy; NOTE: The PROCEDURE SQL printed pages 1-4. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds VARIABLES: 1 1 00926 A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL 1.0 ACTUAL DELIVERY DATE MISSING IN IV VARIABLES: 1 2 00926 A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE 1.5 ACTUAL DELIVERY DATE LATER IN IV VARIABLES: 2 1 02360 A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL 1.0 ACTUAL DELIVERY DATE MISSING IN IV VARIABLES: 2 2 02360 A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE 1.5 ACTUAL DELIVERY DATE LATER IN IV ... View more

My Rule variable values can look like these two examples.  I think that is causing problems with the macro resolution. ZIP5's look fine, but Rule gets messed up maybe because it has special characters or spaces.  I don't undertand what is happening.  Can you take a look?    Two Rule Examples: A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL Do I need to handle a multi word value like this in the code.  Does it have to be quoted somehow?   proc sql; select zip5 into : zip_list separated by ' ' from sampledzips; select rule into : rule_list separated by ' ' from QueryRules; quit; I'm getting weird macro resolutions like this:   SYMBOLGEN: Macro variable ZIP_LIST resolves to 00926 02360 MLOGIC(LOOPY): %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1. MLOGIC(LOOPY): %LET (variable name is ZIP5) SYMBOLGEN: Macro variable ZIP_LIST resolves to 00926 02360 SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable RULE_LIST resolves to A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL MLOGIC(LOOPY): %DO loop beginning; index variable J; start value is 1; stop value is 15; by value is 1. MLOGIC(LOOPY): %LET (variable name is RULE) SYMBOLGEN: Macro variable RULE_LIST resolves to A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL SYMBOLGEN: Macro variable J resolves to 1 MLOGIC(LOOPY): %PUT &i &j &zip5 &rule &rule_order &analysis_desc SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable J resolves to 1 SYMBOLGEN: Macro variable ZIP5 resolves to 00926 SYMBOLGEN: Macro variable RULE resolves to A 1 1 00926 A MLOGIC(LOOPY): %DO loop index variable J is now 2; loop will iterate again. MLOGIC(LOOPY): %LET (variable name is RULE) SYMBOLGEN: Macro variable RULE_LIST resolves to A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL SYMBOLGEN: Macro variable J resolves to 2 MLOGIC(LOOPY): %PUT &i &j &zip5 &rule &rule_order &analysis_desc SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable J resolves to 2 SYMBOLGEN: Macro variable ZIP5 resolves to 00926 SYMBOLGEN: Macro variable RULE resolves to ACTUAL_DLVRY_DATE 1 2 00926 ACTUAL_DLVRY_DATE   ... View more

  This works for me, but not inside an Rsubmit block.  Very stange. ... View more

I appreciate your time so much. I'll get to work on this in the morning. Btw- the reason for each looping, individual query by Rule is that if a record meets the Rule #1 condition then it needs to be excluded from any later rule query. Each record can count toward only the first Rule condition it meets. So I plan to run a query for Rule #1 and then append it to my FinalData ds . Then I plan to apply a Unique index on the Primary Key variable of Imb_code. This will keep records (imb_codes) from later Rules from being appended to FinalData. That's where I'm ultimately headed. Thanks again. I hope I can take your advice and get this thing running. ... View more

Ok, so here's what I'm trying to do inside of the nested loops, but I'm getting an error now saying that there is an Unclosed Do Block. I'm guessing it has to do with the statements I put between the Do and END statements.  Ugh.     As you can see I'm trying to make the ZIP5 and Rule variables from the two different datasets and use them to run the same query substituting them in with different values as the program loops.  So the query would be run for each Rule of each ZIP5.   Can you help?   * Define Oracle libnames ; libname iv_ora oracle user=&orauser pass=&orapass path="IVASPRD" schema="IVAS"; libname bids_ora oracle user=exfcread pass="the#Perfect#P1TA" path="IBSCRPRD" schema="IMAPSSCR"; data QueryData; set sampledZips; %let zip5=zip5; do _n_=1 to _n_queries_; set QueryRules nobs=_n_queries_ point=_n_; %let rule = rule; data ods_iv_recon_selected_mp(where=(imb_dlvry_zip_5='&ZIP5')); set iv_ora.ods_iv_recon_selected_mp; run; data ods_bi_recon_selected_mp (where=(imb_dlvry_zip_5='&ZIP5')); set bids_ora.ods_bi_recon_selected_mp; run; proc sql; create table QueryData%sysfunc(tranwrd(&rule_order,.,_)) as select DISTINCT %str(%')&analysis_desc.%str(%') as RULE_NM length = 58, a.actual_dlvry_date as AD_DT, a.imb_code length = 31, &rule_order as RULE_ORDER from ods_iv_recon_selected_mp a inner join ods_bi_recon_selected_mp b on a.imb_code = b.imb_code where &RULE; quit; end; run;   ... View more

Great. I see the potential with this example.  I'll be working on this again tomorrow so I will try to model after this!  Stay tuned for more dumb questions.   ... View more