Passing Macro Variable Lists

[BCNAV]

I have flight data that has flight plans with waypoints for specific routes.  There are many different routes with different names. Two will be shown below. I need to be able to make route datasets. A route can contain a number of waypoints.  Here is the SAS:

/* Define Route Waypoints */
%let RUBKI_EAST_1 = "RUBKI" "SIKBO" "AHPAH";
%let RUBKI_EAST_2 = "RUBKI" "SIKBO" "TULEG";
%macro swaps_all (route_list=);
	%local n swap_var;
	%do n=1 %to %sysfunc(countw(&route_list));
		%let swap_var=%scan(&route_list,&n);

		proc sql;
			create table egtask.&swap_var as select * from EGTASK.CPL_KTOK
			where flt_biz_uid in (select flt_biz_uid from EGTASK.CPL_KTOK where flt_point_code in (&RUBKI_EAST_1))
			order by flt_biz_uid, msg_biz_uid, flt_point_date;
		quit;

	%end;

%mend;
%swaps_all (route_list=RUBKI_EAST_1 RUBKI_EAST_2);

It all works fine except for passing the new waypoints to:

             where flt_point_code in (&RUBKI_EAST_1))

How can I increment the macro variables for the routes (which can have different names) to that where clause? Or am I going about this all wrong with the macro?  Thanks

[novinosrin]

Aren't you missing the ampersand & here

%swaps_all (route_list=RUBKI_EAST_1 RUBKI_EAST_2);

which, shouldn't it be 

%swaps_all (route_list=&RUBKI_EAST_1 &RUBKI_EAST_2);

[Tom]

Are you tying to use the value of SWAP_VAR as the NAME of the macro variable whose value you want?

%let RUBKI_EAST_1 = "RUBKI" "SIKBO" "AHPAH";
%let swap_var = RUBKI_EAST_1 ;
%put &&&swap_var ;

Like this:

where flt_point_code in (&&&swap_var)

[BCNAV]

I think the &&&swap_var works.  Why do I need 3 &&&?

However, I think the logic is wrong. The staement:

where flt_point_code in (&RUBKI_EAST_1))

is multiple ors.  I want this to be multiple ands.  So in the example:

%let RUBKI_EAST_1 = "RUBKI" "SIKBO" "AHPAH";
%let RUBKI_EAST_2 = "RUBKI" "SIKBO" "TULEG";
%macro swaps_all (route_list=);
	%local n swap_var;
	%do n=1 %to %sysfunc(countw(&route_list));
		%let swap_var=%scan(&route_list,&n);
		%put &&&=swap_var;

		proc sql;
			create table egtask.&swap_var as select * from EGTASK.CPL_KTOK
			where flt_biz_uid in (select flt_biz_uid from EGTASK.CPL_KTOK where flt_point_code in (&RUBKI_EAST_1))
			order by flt_biz_uid, msg_biz_uid, flt_point_date;
		quit;

	%end;

%mend;
%swaps_all (route_list=RUBKI_EAST_1 RUBKI_EAST_2);

You will note that the two routes are similar.  Using the IN in the SQL is multiple ORs.  I do not want this. I want the specific waypoints for the route.  So for:

%let RUBKI_EAST_1 = "RUBKI" "SIKBO" "AHPAH";

 using IN effectively means RUBKI or SIKBO or AHPAH.  Is there a SQL that will make these effectively RUBKI and SIKBO and AHPAH?

thanks

[novinosrin]

Hi @BCNAV  to make it an AND, Can you try 

where flt_biz_uid in
(select flt_biz_uid from EGTASK.CPL_KTOK where flt_point_code in (&RUBKI_EAST_1)
group by flt_biz_uid
having count(distinct flt_pt_code)=countw("&RUBKI_EAST_1",','))

instead of

select * from EGTASK.CPL_KTOK
			where flt_biz_uid in (select flt_biz_uid from EGTASK.CPL_KTOK where flt_point_code in (&RUBKI_EAST_1))
			order by flt_biz_uid, msg_biz_uid, flt_point_date;

Basically the idea is to Count the distinct values in RUBKI_EAST_1 , and if equal to filtered records from the Where

[BCNAV]

So...the solution that seems to work with my macro loop is:

/* Define Route Waypoints */
%let RUBKI_EAST_1 = 'RUBKI' 'SIKBO' 'AHPAH';
%let RUBKI_EAST_2 = 'RUBKI' 'SIKBO' 'TULEG';
%let RUBKI_EAST_3 = 'RUBKI' 'KENLU' 'TULEG';
%let RUBKI_EAST_4 = 'RUBKI' 'SIKBO' 'OLABA' 'SAVAL' 'BUGSY';
%macro swaps_all (route_list=);
	%local n swap_var;
	%do n=1 %to %sysfunc(countw(&route_list));
		%let swap_var=%scan(&route_list,&n);

		proc sql;
			create table egtask.&swap_var as select * from EGTASK.CPL_KTOK
			where flt_biz_uid in (select flt_biz_uid from EGTASK.CPL_KTOK where flt_point_code in (&&&swap_var) group by flt_biz_uid having count(distinct flt_point_code)=countw("&&&swap_var",' '))
			order by flt_biz_uid, msg_biz_uid, flt_point_date;
		quit;

	%end;

%mend;
%swaps_all (route_list=RUBKI_EAST_1 RUBKI_EAST_2 RUBKI_EAST_3 RUBKI_EAST_4);

I have 2 questions:

1.  Why do I need 3 ampersands at: where flt_point_code in (&&&swap_var)

2. For the final part of the where clause it is: having count(distinct flt_point_code)=countw("&&&swap_var",' '). I just put the 3 ampersands there on my own. I have discovered that it will work for 1. How many do I need? 

In general, what is the rule for the amount of & to be used.

Almost there!   Thanks in advance.

[novinosrin]

Though I understand the concepts pretty well, It's only fair and good for us to request the explanation by sage @Tom   whose advise/suggestions have benefitted numerous people including me. So, I am gonna wait with you

In any case, when Tom chimes in with an explanation I would expect most sane participants to listen and collobaorate willy-nilly

[Tom]

When the macro processor sees two ampersands it converts them to one and reminds itself to reprocess that word again.

So you have && mapping to & and &SWAP_VAR mapping to RUBKI_EAST_1. So when does the second pass it sees &RUBKI_EAST_1 and replaces thta.

You can see it in action if you turn on the SYMBOLGEN option.