Nested Looping Frustrations - I just can't see it!

[buechler66]

Just when I thought I had my nested looping problems solved I figured out it wasn't working the way I thought. I'm hoping for some help on structuring a sample program. I need to loop thru each ZIP5 record from the SampledZips dataset. And for each ZIP5 I also need to loop thru each Rule_Order in the QueryRules dataset and do various processing. 

Can anyone help me with this type of Nest Loop structure?  If I can see an example I'm pretty sure I'd be able to build on it.  Any examples would appreciated more than you could know.

data SampledZips;
input zip5 $;
datalines;
00926
02360
;
run;

data QueryRules;
input rule_order $
datalines;
1

2

3

4;
run;

[ballardw]

If you need to get information from the two sets together then

proc sql; 

   create table want as

   select sampledZips.*, QueryRules.*

   from sampledZips, QueryRules;

quit;

You'll get a message about a Cartesian join that can't be optimized but that's what you want if every record in the sampledZips is to be combined with every record in the query rules.

[buechler66]

But this doesn't help me to do the desired looping, right?  Ulitmately I want to do something fancier.  This obviously doesn't work, but I was thinking something like this???

Macro MyZips(zip5= );
 
Data Want;
for i=1 to (however many RuleOrder records there are) do;

     proc sql;
     create table QueryData&zip5 as 
     select imb_code
     from scan_table
     where rule_order = &rule_order
       and imb_dlvry_zip_5 = &zip5;
     ); 
     quit;
end do;
run;
 
%mend MyZips;
data _null_;
set SampledZips;
call execute('%MyZips(zip5='||zip5||');');
run;

[Astounding]

If you want to control the actions of the loops through the programming, here's a way.  It assumes you have created the two initial data sets.

data want;

set sampledZips;

do _n_=1 to _n_queries_;

   set queryRules nobs=_n_queries_ point=_n_;

   * DO something here;

end;

run;

[buechler66]

Cool. I don't understand it tho. What is _n_queries_? _n_? nobs=_n_queries_ point=_n_?

[Astounding]

On the SET statement, a couple of options:

point= instead of retrieving the very next observation each time, look at the value of _N_ and retrieve that observation.

nobs= create a variable holding total number of observations in the data set and, in this case, name it _n_queries_

[buechler66]

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.  

[RW9]

Hi,

I would suggest you get your ideas together, and then post one question stating what the input data is, and what it needs to do, as I mentione din the other post.  The reason is you are mixing various bits of code, doing looping unecessarily.  So far, none of the code posted makes any sense, so its likely your vastly over complicating the task.

[buechler66]

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;

[Astounding · Accepted solution]

I'm torn.  On the one hand, you've put together some good pieces of the final program.  On the other hand, there are many reasons why this program hasn't a prayer.  I'm going to try to redirect your approach, while letting you keep much of what you have done already.  Consider this macro (and debug it if necessary ... it's untested):

%macro loopy;

%local i j zip5 rule zip_list rule_list;

proc sql;

select zip5 into : zip_list separated by ' ' from sampledzips;

select rule into : rule_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);

            %put &i &j &zip5 &rule;

   %end;

%end;

%mend loopy;

%loopy

For now, the %PUT statement is just illustrating how the loops work.  It gets replaced with two DATA steps and a SQL step (although I'm convinced that a good SQL programmer ... that leaves me out ... could condense this into a single SQL step).

Some notes about what you will encounter along the way.  

Single quotes suppress all macro processing.  So '&ZIP5' will not allow &ZIP5 to resolve.  

I'm not sure where &ANALYSIS_DESC comes from.  It looks like it must exist before all this programming occurs.

SQL parses quoted quotes differently, so this will not work:

%str(%')&analysis_desc.%str(%')

You will have to remove the "quoted" nature of the quotes:

%unquote(%str(%')&analysis_desc.%str(%'))

This should be enough of a framework that any remaining issues can be worked out.

Good luck. 

[buechler66]

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

[Astounding]

There can be issues with a macro variable's value being created on one side (local vs. remote) needing to be transferred to the other side.  There are tools to deal with that ... I think they are %SYSLPUT and %SYSLGET but I use this so rarely that I'm not really sure.

If there's a different issue, it might help to share the log.

[buechler66]

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

[buechler66]

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

[Astounding]

Good job.  Notice a small technical detail.

Macro language does not use quotes ... in most cases, it assumes everything is a character string.  So you only need # (not '#') as a list of delimiters.  When you use '#' instead, that's using both pound signs and single quotes as delimiters.  That's unlikely to have an impact, but it's possible.