topic Re: Passing Macro Variable Lists in SAS Programming

Passing Macro Variable Lists

BCNAV — Sat, 22 Feb 2020 20:22:35 GMT

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

Re: Passing Macro Variable Lists

novinosrin — Sat, 22 Feb 2020 21:08:18 GMT

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);

Re: Passing Macro Variable Lists

Tom — Sat, 22 Feb 2020 21:27:27 GMT

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)

Re: Passing Macro Variable Lists

BCNAV — Sat, 22 Feb 2020 22:58:32 GMT

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

Re: Passing Macro Variable Lists

novinosrin — Sat, 22 Feb 2020 23:15:25 GMT

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

Re: Passing Macro Variable Lists

BCNAV — Sun, 23 Feb 2020 03:16:28 GMT

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.

Re: Passing Macro Variable Lists

novinosrin — Sun, 23 Feb 2020 04:52:10 GMT

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

Re: Passing Macro Variable Lists

Tom — Sun, 23 Feb 2020 05:18:37 GMT

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.