Checking for Existence of a Variable...Then Action Based on Result

[BCNAV]

I have the following code that checks for air traffic in markets:

PROC SQL;
   CREATE TABLE EGTASK.OVR_WCU_MKTS_FINAL AS 
   SELECT t1.FltDate, 
          (t1.curr_mkt_11 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_11, 
          (t1.curr_mkt_14 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_14, 
          (t1.curr_mkt_22 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_22, 
          (t1.curr_mkt_23 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_23, 
          (t1.curr_mkt_24 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_24, 
          (t1.curr_mkt_30 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_30, 
          /* curr_mkt_60 */
            (t1.curr_mkt_60 * &br_ovr) FORMAT=COMMA20.0 AS curr_mkt_60, 

            (t2.prev_mkt_11 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_11, 
            (t2.prev_mkt_14 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_14, 
            (t2.prev_mkt_22 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_22, 
            (t2.prev_mkt_23 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_23, 
            (t2.prev_mkt_24 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_24, 
            (t2.prev_mkt_30 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_30, 
          /* prev_mkt_60 */
            (t2.prev_mkt_60 * &br_ovr) FORMAT=COMMA20.0 AS prev_mkt_60

      FROM EGTASK.OVR_CURR_MKT_FINAL t1
           INNER JOIN EGTASK.OVR_PREV_MKT_FINAL t2 ON (t1.FltDate = t2.FltDate)
      ORDER BY t1.FltDate;
QUIT;

The problem curr references the current year and prev references previous year.

Market 60 is the "Other Market" and the issue is that it may not exist for a given time frame. As of today (October 7th), there are no flights either in the current year or the previous year for this market, so the SQL will bomb for the market 60 calculations above since there are no 60 variables in the source tables. How would I check to see if they exist in the source data (t1 or t2 above) and if they don't exist, set them equal to 0.  That is, create curr_mkt_60 and/or prev_mkt_60 and set equal to 0 if needed.

[Shmuel · Accepted solution]

Add two new steps before your sql code:

data tmp_ovr_curr_mkt;
       format curr_mkt_11 curr_mkt_14 ... /* full list of output variables */ comma2.0;
  set egtask.ovr_curr_mkt_final;
       keep <list of only need variables > ;
run;
              
data tmp_ovr_prev_mkt;
       format curr_mkt_11 curr_mkt_14 ... /* full list of output variables */ comma2.0;
  set egtask.ovr_prev_mkt_final;
      keep <list of only need variables > ;
run;  

thus you have created all variables to exist, ahead.

use the tmp_ datasets instead the original.

[ballardw]

This  sort of issue is one of the reasons data should not be in variable names. If your data were structured to have a MARKET variable then joining on the market variable along with the date would reduce this to something like:

PROC SQL;
   CREATE TABLE EGTASK.OVR_WCU_MKTS_FINAL AS 
   SELECT t1.FltDate,market, curr 
          (t1.curr_mkt * &br_ovr) FORMAT=COMMA20.0 AS curr_, 
          (t2.prev_mkt * &br_ovr) FORMAT=COMMA20.0 AS prev_, 
      FROM EGTASK.OVR_CURR_MKT_FINAL t1
           INNER JOIN EGTASK.OVR_PREV_MKT_FINAL t2 
          ON (t1.FltDate = t2.FltDate and t1.market=t2.market)
      ORDER BY t1.FltDate;
QUIT;

Which wouldn't have issues that might arise when you have some other market temporarily out of the data, such as an airline strike or extended weather event, or when a new market is added (Kamchatka anyone)

[BCNAV]

Yes...having better data is always good, but we don't always have control over that. The first solution is simple and elegant....I always know what markets exist, just not if there is data or not!

[ChrisNZ]

You could create a view:

data _V_ALL_COLUMNS/view=_V_ALL_COLUMNS;
  retain CURR_MKT_60 PREV_MKT: PREV_MKT_60 0;
  set EGTASK.OVR_PREV_MKT_FINAL;
run;

and query that view.

If you want the columns at the end, you can use a format statement as per @Shmuel , or something like

data _V_ALL_COLUMNS/view=_V_ALL_COLUMNS;
  if 0 then set EGTASK.OVR_PREV_MKT_FINAL;
  retain CURR_MKT_60 PREV_MKT: PREV_MKT_60 0;
  set EGTASK.OVR_PREV_MKT_FINAL;
run;