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
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);
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)
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
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
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.
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: