I have data that looks like:   FLT_BIZ_UID MSG_BIZ_UID DATA_LINK_IND ICOMM_IND EDM_DATALINK GANDER_DATALINK GANDER_VOICE 36184893 2758785634     0 0 0 36184893 2758845155     0 0 0 36184893 2758903711 Y   0 1 0 36184893 2758903960   Y 0 0 1 36184893 2758955914 Y   0 1 0 36184893 2759004380 Y   0 1 0 36184894 2758785634     0 0 0 36184894 2758845155     0 0 0 36184894 2758903711   Y 0 0 1 36184894 2758903960   Y 0 0 1 36184894 2758955914     0 0 0 36184894 2759004380     0 0 0   Every flt_biz_uid can have 1 or more messages (msg_biz_uid). I need to select flt_biz_uids that are voice only. Those that are voice only ALWAYS have DATA_LINK_IND missing, ICOMM_IND=Y, EDM_DATALINK=0, GANDER_DATALINK=0, and  GANDER_VOICE=1.   So in the example above, flt_biz_uid 36184893 would not be chosen, but 36184894 would be. Would there be something in sql that would get his done?   Thx! ... View more

We have data going back many years. As time has moved forward, one variable has been removed as it is no longer being used for billing purposes. This variable is called nbday. I am creating a reporting tool that needs to go back in time to when this variable existed. Each dataset is based on a fiscal year basis (not made by me), hence nbday may be in it or it may not be.   So I need to take each individual fiscal year file (I never know which year I will need) and check to see the existence of nbday. If it does not exist, create it and give it a value of 1. If it does exist, keep it at the value it has.  Something like:   data egtask.test;      set fiscal2019;      if nbday does not exist, then create nbday and set equal to 1;      if nbday exists, then keep it; run;     Or maybe sql?  case when nbday does not exist then nbday=1 else nbday?     No sure this is possible or not in a datastep.   thanks -Bill ... View more

I have the following in a macro:   %let date2 = %sysfunc(intnx(month,%sysfunc(today()),-15,end));     I would like to replace the today() with a custom field for a date:   %let yearuse = 2019; %let monthuse = 3; %let date2 = %sysfunc(intnx(month,%sysfunc(today()),-15,end));   What would I replace the today() above with?  That is, I want 15 months back from the 2019 month 3 above it.   Thx! ... View more

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

I have a variable called NAME   In it are geographical locations all in CAPS   Example:   LESTER B. PEARSON VAL-DOR NORTH BAY   Is there a SAS format for text that will convert these to:   Lester B. Pearson Val-Dor North Bay   thanks     ... View more