error says macro variable is a character but I don't see how that is possible

[logistics_00]

Hi,

I have run this program in the  past with no issues, but suddenly I'm getting errors like this:

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.

But if you look at my code, the macro variable &mails is a count distinct of another field, so how can it not be a number? I feel like I'm going a little bit nuts. 

%libdb;
%liblr;
%libee;
/*%pfile(P3 easybird);*/



proc sql;


create table easybird as
select distinct

b.source,
strip(put(b.l_nbr,10.)) as listing_id,
b.ls_d format = MMDDYYs. as close_date,
today() - b.ls_d as close_days,/*how many days since the listing closed*/
b.cc_name,
b.job_title,
b.cm as cm_name,
e.email_address as cm_email_address,
b.am as am_name,
g.director as director_name,
h.email as director_email,
f.email as am_email,
case when b.source = 'US' 
then 'http://compass.oxfordcorp.com/#listing/'||strip(put(b.jo_url_id, 10.)) 
else 'http://compass.eu.oxfordcorp.com/#listing/'||strip(put(b.jo_url_id,10.)) end as url /*creates url field for body of email*/
	


from lr.jos b
inner join stg.cm_email e
on b.cm_id = e.cm_id
and e.is_primary <> 0
inner join ee.active_empl f
on b.am_eid = f.eid
inner join ee.dept_org_map g
on f.dept_id = g.dept_id 
and scope = 'BD'
and cur = 1
inner join ee.active_empl h
on g.director_eid = h.eid 


where today()-b.ls_d in (10,30) /*includes in only listings that closed 10 or 30 days ago*/
and b.comp_fill = 1 /*includes in only comp fills*/
and b.open is null


order by b.ls_d desc
			
;quit;


%macro easybird;
	proc sql noprint;
		/*counts the number of listings to mail to*/
		select count(distinct listing_id)
		into :mails
		from easybird
		;
	
		/*lists the listings and puts them in variables*/
		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_%sysfunc(strip(&mails)),
			:am_1 - :am_%sysfunc(strip(&mails)),
			:dn_1 - :dn_%sysfunc(strip(&mails)),
			:de_1 - :de_%sysfunc(strip(&mails)),
			:cm_1 - :cm_%sysfunc(strip(&mails)),
			:cc_1 - :cc_%sysfunc(strip(&mails)),
			:title_1 - :title_%sysfunc(strip(&mails)),
			:listing_1 - :listing_%sysfunc(strip(&mails)),
			:url_1 - :url_%sysfunc(strip(&mails)),
			:days_1 - :days_%sysfunc(strip(&mails))
			from easybird
		;
	quit;

	
%do i = 1 %to &mails;	
		%let l_nbr = %superq(listing_&i);
		%let am_email = %superq(mail_&i);
		%let dir_email = %superq(de_&i);
		%let cls_days = %superq(days_&i);
		filename outbox email 
			to= 'jennifer_boyle@oxfordcorp.com' /*"&am_email"*/
			/*cc= 'jennifer_boyle@oxfordcorp.com' "&dir_email"*/
			/*bcc= ('jennifer_boyle@oxfordcorp.com''steven_kim@oxfordcorp.com')*/
			type = 'text/html'
			from = 'Oxford Marketing <marketing@oxfordcorp.com>'
			sender = 'Oxford Marketing <marketing@oxfordcorp.com>'
			subject = "Comp Fill &cls_days - Day Notice - CM Follow-Up with Listing #&l_nbr"
		;
			%let am_f_name = %superq(am_&i);
			%let client_manager = %superq(cm_&i);
			%let comp = %superq(cc_&i);
			%let jt = %superq(title_&i);
			%let url = %superq(url_&i);
				
			data _null_;
			file outbox;
			put "Hello &am_f_name," @;
			put ' ';
				/*   ----+----1----+----2----+----3----+----4----+----5----+----6 */
				put '<br>';
				put '<br>';
				put "Listing <a href = &url >&l_nbr</a> closed &cls_days days ago due to " @;
				put 'comp fill.';
				put '<br>';
				put 'Please follow up with the CM to check on the project status, ' @;
				put 'comp performance, and new opportunities.';
				put '<br>';
				put '<br>';
				put "Client Manager: <b>&client_manager </b>" ;
				put'<br>';
				put "Company: <b>&comp </b>" ;
				put'<br>';
				put "Job Title: <b>&jt </b>" ;
				put '<br>';
				put '<br>';
				put 'Sincerely,';
				put '<br>';
				put '<br>';
				put 'Oxford Marketing Department';
				put '<br>';
				put '<br>';
				put "General questions? Contact <a href='mailto:marketing@oxfordcorp.com?Subject=Comp Fill Reminder Listing # &l_nbr ' target='_top'>marketing@oxfordcorp.com</a>" ;
				put '<br>';
				put "Data related questions? Contact <a href='mailto:bi_support@oxfordcorp.com?Subject=Comp Fill Reminder Listing # &l_nbr ' target='_top'>BI_support@oxfordcorp.com</a>" ;
				

				/*   ----+----1----+----2----+----3----+----4----+----5----+----6 */
		run;
	%end;
%mend easybird;
option nonotes noquotelenmax;
%dev_prod(, %nrstr(%easybird), macro);
option notes quotelenmax;
%macro send_email;

proc sql noprint;
		/*counts the number of listings tho mail to*/
		select count(distinct listing_id)
		into :mails
		from easybird
		;
	
/* SEND EMAIL IF THERE IS DATA TODAY OR NOT */
	%if &mails >= 1 %then %do;
		%let data_today = 1;
	%end;
	%else %do;
		%let data_today = 0;
	%end;

/* IF THERE IS DATA TODAY*/
	%if &data_today = 1 %then %do;

	filename mymail email 'bi@oxfordcorp.com'
				to = ('bi@oxfordcorp.com''jennifer_boyle@oxfordbi.com' 'steven_kim@oxfordbi.com')
		subject = 'Easybird Count';
	;
	data _null_;
		file mymail;
		put 'Hi,';
		put ' ';
		put 'The Easybird script ran successfully ';
		put 'today: '"%sysfunc(today(), date10.)";
		put ' ';
		put 'It contains the following data:';
		put 'Number of listings:  '"&mails";
		put ' ';
		put 'Thank you,';
		put 'Jennifer';
	run;
	%end;
	%else %do;
/*IF THERE IS NO DATA TODAY*/
	filename mymail email 'bi@oxfordcorp.com'
				to = ('bi@oxfordcorp.com''jennifer_boyle@oxfordcorp.com''steven_kim@oxfordbi.com')
		subject = 'Easybird Count';
	;
	data _null_;
		file mymail;
		put 'Hi,';
		put ' ';
		put 'The Easybird script ran sucessfully today: '"%sysfunc(today(), date10.)";
		put ' ';
		put 'But there are no listings.';
		put ' ';
		put 'Let me know if you have any questions.';
		put ' ';
		put 'Thank you,';
		put ' ';
		put 'Jennifer';

	run;
	%end;
%mend send_email;

option nonotes noquotelenmax;
%dev_prod(, %nrstr(%send_email), macro);
option notes quotelenmax;
/*%plog;*/

[Reeza]

Run the macro with the debugging options on and post the log.

options mprint symbolgen;

What does a %PUT show for the variable in question?

[logistics_00]

[Kurt_Bremser]

You'll also see a WARNING that a symbolic reference could not be resolved. This happens because there are no observations in easybird, so the macro variable is not created.

Set the macro variable to 0 before the select ... into.

[logistics_00]

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

[Tom]

Your code is too convoluted to follow exactly.  

You are using PROC SQL to generate the MAILS macro variable.  That can fail in two ways. 

  (A) It could not find any values and hence not assign any value to the macro variable. So it will keep its old value or just not exist.

  (B) It could be so large that SAS will generate scientific notation when setting the value.

Make sure to set a value before the SELECT statement and tell SAS to use a longer format when writing the value. While you are at it remove the leading/trailing spaces.

%let mails=0;
select count(distinct listing_id) format=32.
  into :mails trimmed
from easybird
;

Or better eliminate that extra query and just use the SQLOBS automatic macro variable generated from your real query.

select distinct
	am_email,
	am_name,
	director_name,
	director_email,
	cm_name,
	cc_name,
	job_title,
	listing_id,
	url,
	close_days
into
	:mail_1 - 
	:am_1 - 
	:dn_1 - 
	:de_1 - 
	:cm_1 - 
	:cc_1 - 
	:title_1 - 
	:listing_1 - 
	:url_1 - 
	:days_1 - 
from easybird
;
%let mails=&sqlobs;

[logistics_00]

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

[Tom]

---

@logistics_00 wrote:

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

---

What does that mean? 

Is the 0 causing trouble? Does your logic properly handle when there are no emails to send?  You might need an %IF &MAILS>0 %THEN %DO;... %END; block around some or all of the later steps.

Or do you not understand why the query is not finding any matching observations?  What do the notes SAS?

If you are using CALL EXECUTE() then watch out for timing issues where code is generated before the query that sets it can run.

[logistics_00]

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. 

[Kurt_Bremser]

---

@logistics_00 wrote:

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. 

---

But does it have any non-missing values in the column you count? Run a proc freq for this variable.

[logistics_00]

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. 

[Reeza]

Please post the log as text. There's something weird there as the errors are coming before you declare or calculate the macro variable so either the code shown isn't what's run or you messed up your session somehow. I'm assuming you've done the restart and run from scratch to get a clean log check already.

[logistics_00]

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;*/

[Kurt_Bremser]

I see what's happening, it's a macro timing mistake.

Use %nrstr( in the call execute, to prevent premature execution of macro statements.

[Reeza]

But the OP said this worked previously which is the part that confuses me.