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 ... View more

I have some airport departure and destination data, along with charges.  Suppose I have:   depart     dest        OCU cyyz       egll        500 cyvr       cyyz         10 egll       cyyz        500       What I would like to do is to sum OCU by city pair, which is the concatenation of depart and dest, hopefully in the format of depart-dest (dash in between them).   The price is that reverse flights should be included as one...so above the final output dataset would be:   City_Pair     OCU cyyz-egll     1000 cyvr-cyyz       10     thanks for any advice!     ... View more

Suppose I know the Lat and Long of an origin and destination airport. I then want to find the main direction of flight.   Example:   EIDW DUBLIN INTERNATIONAL Lat: 53.4213888889 Long: -6.2700000000   TO:   CYYZ LESTER B. PEARSON INTL Lat: 43.6767190556 Long: -79.6306582222     In this case I would like to return West. The reverse flight from CYYZ to EIDW would return East Not sure SAS can do this or not. The preference would be only returning North, East, South, West     Thanks for any ideas   ... View more

I have 2 datasets that have the SAME variables, but some of those variables may have values and some may not. The key variable between them is billable_id (it always has data in it). I had thought of doing something like:   PROC SQL; CREATE TABLE EGTASK.TEST AS SELECT (case when t1.billable_id is missing then t2.billable_id else t1.billable_id end) AS billable_id, (case when t1.ac_type is missing then t2.ac_type else t1.ac_type end) AS ac_type FROM EGTASK.QUERY_FOR_Q_FOR_BILLING_DAT_000E t1 FULL JOIN EGTASK.QUERY_FOR_Q_FOR_BILLING_DAT_000F t2 ON (t1.billable_id = t2.billable_id); QUIT; But for many variables this case when approach will take time. Is there a way to tell SAS to do this for all variables as opposed to typing everything out and risking typos?     Thanks ... View more

When we bill aircraft, there are a number of systems that do the billing. There is one variable that will tie together the data from the systems. This variable is called the billing_flight_id. Suppose I have data that is:   billable_id FLT_BIZ_UID fdaas_flight_id billing_flight_id chargetype_code ac_type synonym more variables 97975800 33614638 33614638 23377506 M A343 A343   97975800 33614638 33614638 23377506 M A343 A343   97988982 . . 23377506 T A343     98028109 . . 23377506 N A343 H/A343/*     You can see that the billing_flight_id is the same for all 3 systems of billing charges (M,T,N). What I would like to do is:   For every billing_flight_id, if there is a FLT_BIZ_UID and fdaas_flight_id present as part of its billing process, fill in the missing FLT_BIZ_UID and fdaas_flight_id with the existing values of those variables. All other variables would be the same. So my output would be:   billable_id FLT_BIZ_UID fdaas_flight_id billing_flight_id chargetype_code ac_type synonym more variables 97975800 33614638 33614638 23377506 M A343 A343   97975800 33614638 33614638 23377506 M A343 A343   97988982 33614638 33614638 23377506 T A343     98028109 33614638 33614638 23377506 N A343 H/A343/*     The bold are the changes.   Thanks in advance! ... View more