This was exactly the issue. The %dev_prod macro determines if a script is being run on our dev server or our prod server and treats it accordingly (like the dev server doesn't have an email application). I thought they were identical environments, but taking out that macro resolves my issues.  I'll have to take that up with the DW architect but for now I'm gtg.    Thank you so much for your help, everyone. Much appreciated.    Jennifer  ... View more

option nonotes noquotelenmax; %dev_prod(, %nrstr(%easybird), macro); option notes quotelenmax; Don't I already have this though? Sorry if I'm not understanding.  ... View more

2189 proc sql; 2190 2191 2192 create table easybird as 2193 select distinct 2194 2195 b.source, 2196 strip(put(b.l_nbr,10.)) as listing_id, 2197 b.ls_d format = MMDDYYs. as close_date, 2198 today() - b.ls_d as close_days,/*how many days since the listing closed*/ 2199 b.cc_name, 2200 b.job_title, 2201 b.cm as cm_name, 2202 e.email_address as cm_email_address, 2203 b.am as am_name, 2204 g.director as director_name, 2205 h.email as director_email, 2206 f.email as am_email, 2207 case when b.source = 'US' 2208 then 'http://compass.oxfordcorp.com/#listing/'||strip(put(b.jo_url_id, 10.)) 2209 else 'http://compass.eu.oxfordcorp.com/#listing/'||strip(put(b.jo_url_id,10.)) end as url 2209! /*creates url field for body of email*/ 2210 2211 2212 2213 from lr.jos b 2214 inner join stg.cm_email e 2215 on b.cm_id = e.cm_id 2216 and e.is_primary <> 0 2217 inner join ee.active_empl f 2218 on b.am_eid = f.eid 2219 inner join ee.dept_org_map g 2220 on f.dept_id = g.dept_id 2221 and scope = 'BD' 2222 and cur = 1 2223 inner join ee.active_empl h 2224 on g.director_eid = h.eid 2225 2226 2227 where today()-b.ls_d in (10,30) /*includes in only listings that closed 10 or 30 days ago*/ 2228 and b.comp_fill = 1 /*includes in only comp fills*/ 2229 and b.open is null 2230 2231 2232 order by b.ls_d desc 2233 2234 ; NOTE: The "<>" operator is interpreted as "not equals". NOTE: Table WORK.EASYBIRD created, with 10 rows and 13 columns. 2234! quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.64 seconds cpu time 0.20 seconds 2235 2236 2237 %macro easybird; 2238 2239 proc sql; 2240 /*counts the number of listings to mail to*/ 2241 select count(distinct listing_id)format=32. 2242 into :mails trimmed 2243 from easybird 2244 ; 2245 2246 /*lists the listings and puts them in variables*/ 2247 select distinct 2248 am_email, 2249 am_name, 2250 director_name, 2251 director_email, 2252 cm_name, 2253 cc_name, 2254 job_title, 2255 listing_id, 2256 url, 2257 close_days 2258 into 2259 :mail_1 - :mail_%sysfunc(strip(&mails)), 2260 :am_1 - :am_%sysfunc(strip(&mails)), 2261 :dn_1 - :dn_%sysfunc(strip(&mails)), 2262 :de_1 - :de_%sysfunc(strip(&mails)), 2263 :cm_1 - :cm_%sysfunc(strip(&mails)), 2264 :cc_1 - :cc_%sysfunc(strip(&mails)), 2265 :title_1 - :title_%sysfunc(strip(&mails)), 2266 :listing_1 - :listing_%sysfunc(strip(&mails)), 2267 :url_1 - :url_%sysfunc(strip(&mails)), 2268 :days_1 - :days_%sysfunc(strip(&mails)) 2269 from easybird 2270 ; 2271 2272 quit; 2273 2274 2275 %do i = 1 %to &mails; 2276 %let l_nbr = %superq(listing_&i); 2277 %let am_email = %superq(mail_&i); 2278 %let dir_email = %superq(de_&i); 2279 %let cls_days = %superq(days_&i); 2280 filename outbox email 2281 to= 'jennifer_boyle@oxfordcorp.com' /*"&am_email"*/ 2282 /*cc= 'jennifer_boyle@oxfordcorp.com' "&dir_email"*/ 2283 /*bcc= ('jennifer_boyle@oxfordcorp.com''steven_kim@oxfordcorp.com')*/ 2284 type = 'text/html' 2285 from = 'Oxford Marketing <marketing@oxfordcorp.com>' 2286 sender = 'Oxford Marketing <marketing@oxfordcorp.com>' 2287 subject = "Comp Fill &cls_days - Day Notice - CM Follow-Up with Listing #&l_nbr" 2288 ; 2289 %let am_f_name = %superq(am_&i); 2290 %let client_manager = %superq(cm_&i); 2291 %let comp = %superq(cc_&i); 2292 %let jt = %superq(title_&i); 2293 %let url = %superq(url_&i); 2294 2295 data _null_; 2296 file outbox; 2297 put "Hello &am_f_name," @; 2298 put ' '; 2299 /* ----+----1----+----2----+----3----+----4----+----5----+----6 */ 2300 put '<br>'; 2301 put '<br>'; 2302 put "Listing <a href = &url >&l_nbr</a> closed &cls_days days ago due to " @; 2303 put 'comp fill.'; 2304 put '<br>'; 2305 put 'Please follow up with the CM to check on the project status, ' @; 2306 put 'comp performance, and new opportunities.'; 2307 put '<br>'; 2308 put '<br>'; 2309 put "Client Manager: <b>&client_manager </b>" ; 2310 put'<br>'; 2311 put "Company: <b>&comp </b>" ; 2312 put'<br>'; 2313 put "Job Title: <b>&jt </b>" ; 2314 put '<br>'; 2315 put '<br>'; 2316 put 'Sincerely,'; 2317 put '<br>'; 2318 put '<br>'; 2319 put 'Oxford Marketing Department'; 2320 put '<br>'; 2321 put '<br>'; 2322 put "General questions? Contact <a 2322! href='mailto:marketing@oxfordcorp.com?Subject=Comp Fill Reminder Listing # &l_nbr ' 2322! target='_top'>marketing@oxfordcorp.com</a>" ; 2323 put '<br>'; 2324 put "Data related questions? Contact <a 2324! href='mailto:bi_support@oxfordcorp.com?Subject=Comp Fill Reminder Listing # &l_nbr ' 2324! target='_top'>BI_support@oxfordcorp.com</a>" ; 2325 2326 2327 /* ----+----1----+----2----+----3----+----4----+----5----+----6 */ 2328 run; 2329 %end; 2330 %mend easybird; 2331 option nonotes noquotelenmax; 2332 %dev_prod(, %nrstr(%easybird), macro); SYMBOLGEN: Macro variable SYSHOSTNAME resolves to PRD-BIA-APP-003 SYMBOLGEN: Macro variable SYSHOSTNAME resolves to PRD-BIA-APP-003 SYMBOLGEN: Macro variable TASK_TYPE resolves to macro MPRINT(DEV_PROD): data _null_; SYMBOLGEN: Macro variable PROD_TASK resolves to %easybird SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(DEV_PROD): call execute ("%easybird"); MPRINT(DEV_PROD): run; MPRINT(EASYBIRD): proc sql; MPRINT(EASYBIRD): select count(distinct listing_id)format=32. into :mails trimmed from easybird ; WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. WARNING: Apparent symbolic reference MAILS not resolved. MPRINT(EASYBIRD): select distinct am_email, am_name, director_name, director_email, cm_name, cc_name, job_title, listing_id, url, close_days into :mail_1 - :mail_&mails, :am_1 - :am_&mails, :dn_1 - :dn_&mails, :de_1 - :de_&mails, :cm_1 - :cm_&mails, :cc_1 - :cc_&mails, :title_1 - :title_&mails, :listing_1 - :listing_&mails, :url_1 - :url_&mails, :days_1 - :days_&mails from easybird ; MPRINT(EASYBIRD): quit; WARNING: Apparent symbolic reference MAILS not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &mails ERROR: The %TO value of the %DO I loop is invalid. ERROR: The macro EASYBIRD will stop executing. 1 + proc sql; 1 + select count(distinct listing_id)format=32. into :mails trimmed from easybird ; 1 + select distinct am_email, am_name, director_name, 2 + director_email, cm_name, cc_name, job_title, listing_id, url, close_days into :mail_1 - :mail_&mails, :am_1 - :am_&mails, :dn_1 - SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 3 + :dn_&mails, :de_1 - :de_&mails, :cm_1 - :cm_&mails, :cc_1 - :cc_&mails, :title_1 - :title_&mails, :listing_1 - :listing_&mails, :url_1 - :url_&mails, :days_1 - SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 SYMBOLGEN: Macro variable MAILS resolves to 10 4 + :days_&mails from easybird ; quit; SYMBOLGEN: Macro variable MAILS resolves to 10 MPRINT(DEV_PROD): mail_10am_10dn_10de_10cm_10cc_10title_10listing_10url_10days_10 2333 option notes quotelenmax; 2334 options mprint symbolgen; 2335 %macro send_email; 2336 proc sql; 2337 /*counts the number of listings to mail to*/ 2338 select count(distinct listing_id)format=32. 2339 into :mails trimmed 2340 from easybird 2341 ; 2342 2343 /* SEND EMAIL IF THERE IS DATA TODAY OR NOT */ 2344 %if &mails >= 1 %then %do; 2345 %let data_today = 1; 2346 %end; 2347 %else %do; 2348 %let data_today = 0; 2349 %end; 2350 2351 /* IF THERE IS DATA TODAY*/ 2352 %if &data_today = 1 %then %do; 2353 2354 filename mymail email 'bi@oxfordcorp.com' 2355 to = ('bi@oxfordcorp.com''jennifer_boyle@oxfordbi.com' 2355! 'steven_kim@oxfordbi.com') 2356 subject = 'Easybird Count'; 2357 ; 2358 data _null_; 2359 file mymail; 2360 put 'Hi,'; 2361 put ' '; 2362 put 'The Easybird script ran successfully '; 2363 put 'today: '"%sysfunc(today(), date10.)"; 2364 put ' '; 2365 put 'It contains the following data:'; 2366 put 'Number of listings: '"&mails"; 2367 put ' '; 2368 put 'Thank you,'; 2369 put 'Jennifer'; 2370 run; 2371 %end; 2372 %else %do; 2373 /*IF THERE IS NO DATA TODAY*/ 2374 filename mymail email 'bi@oxfordcorp.com' 2375 to = 2375! ('bi@oxfordcorp.com''jennifer_boyle@oxfordcorp.com''steven_kim@oxfordbi.com') 2376 subject = 'Easybird Count'; 2377 ; 2378 data _null_; 2379 file mymail; 2380 put 'Hi,'; 2381 put ' '; 2382 put 'The Easybird script ran successfully today: '"%sysfunc(today(), date10.)"; 2383 put ' '; 2384 put 'But there are no listings.'; 2385 put ' '; 2386 put 'Let me know if you have any questions.'; 2387 put ' '; 2388 put 'Thank you,'; 2389 put ' '; 2390 put 'Jennifer'; 2391 2392 run; 2393 %end; 2394 %mend send_email; 2395 2396 option nonotes noquotelenmax; 2397 %dev_prod(, %nrstr(%send_email), macro); SYMBOLGEN: Macro variable SYSHOSTNAME resolves to PRD-BIA-APP-003 SYMBOLGEN: Macro variable SYSHOSTNAME resolves to PRD-BIA-APP-003 SYMBOLGEN: Macro variable TASK_TYPE resolves to macro MPRINT(DEV_PROD): data _null_; SYMBOLGEN: Macro variable PROD_TASK resolves to %send_email SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(DEV_PROD): call execute ("%send_email"); MPRINT(DEV_PROD): run; MPRINT(SEND_EMAIL): proc sql; MPRINT(SEND_EMAIL): select count(distinct listing_id)format=32. into :mails trimmed from easybird ; WARNING: Apparent symbolic reference MAILS not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &mails >= 1 ERROR: The macro SEND_EMAIL will stop executing. 1 + proc sql; 1 + select count(distinct listing_id)format=32. into :mails trimmed from easybird ; 2398 option notes quotelenmax; 2399 options mprint symbolgen; 2400 /*%plog;*/ ... View more

Any non-missing values? They are all non-missing as it's a mandatory column in the data set. So there are no missing values, ever, if that's what you're asking. Listing_id is always populated.  ... View more

There are ten observations in the data set easybird! There are more than zero observations 99% of the days it runs.  ... View more

There are definitely 10 observations in the dataset easybird. So if &mails is resolving to 0 when I run the script, it means that the rest of the variables don't load. It sends ten emails with blanks instead of values.  ... View more

I think this is the right path, but if I now the &mail is resolving to 0 every time?   ... View more

It's a good thought, but there are 10 observations. Never more than like, 50 under any circumstances.  ... View more

Perfect. Turns out I was asking the wrong question, I should have started with how do I calculate these percentiles. Much appreciated! ... View more