Hi. I'm getting the error "WARNING: Apparent symbolic reference ZIP5 not resolved." in my log and need some help to understand how to fix the issue. My outter macro's macro variable won't resolve inside my inner macro. Any help would be greatly appreciated. (log and program below).
378 data SampledZips;
379 input zip5 $;
380 datalines;
NOTE: The data set WORK.SAMPLEDZIPS has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 687.35k
OS Memory 12424.00k
Timestamp 06/13/2016 10:20:00 AM
383 ;
384 run;
385
386
387 * Build dataset from Oracle table that contains matching rules ;
388 proc sql;
389 connect to oracle as db (user=&orauser password=&orapass path="ivasprd");
390 create table QueryRules as
391 select * from connection to db
392 ( select analysis_desc,
393 rule,
394 rule_order
395 from ivprl.analysis_ctrl
396 where trunc(upload_create_dt) = trunc(sysdate)
397 and rule_order not in(400)
398 and rule_order < 5
399 order by rule_order desc
400 );
NOTE: Table WORK.QUERYRULES created, with 9 rows and 3 columns.
401 disconnect from db;
402 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.75 seconds
user cpu time 0.03 seconds
system cpu time 0.03 seconds
memory 1106.05k
OS Memory 12796.00k
Timestamp 06/13/2016 10:20:01 AM
403
404
405 %MACRO BuildZIP5QueryData(zip5= );
406 * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ;
407 %MACRO BuildQueryData(analysis_desc= , rule= , rule_order= );
408
409 %PUT &ZIP5 &RULE_ORDER;
410
411 %MEND BuildQueryData;
412 * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the
412! dataset ;
413 data _null_;
414 set QueryRules;
415 call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' ,
415! rule_order='||rule_order||');');
416 run;
417
418 %MEND BuildZIP5QueryData;
419 * Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset
419! ;
420 data _null_;
421 set SampledZips;
422 call execute('%BuildZIP5QueryData(zip5='||zip5||');');
423 run;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 677.57k
OS Memory 12424.00k
Timestamp 06/13/2016 10:20:01 AM
NOTE: There were 2 observations read from the data set WORK.SAMPLEDZIPS.
NOTE: CALL EXECUTE generated line.
1 + * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ;
* Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ;
2 + data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' ,
rule='||rule||' , rule_order='||rule_order||');'); run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
2:153
WARNING: Apparent symbolic reference ZIP5 not resolved.
2 +
;
&ZIP5 4
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.5
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.3
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.2
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.1
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 2
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 1.5
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 1
NOTE: There were 9 observations read from the data set WORK.QUERYRULES.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 714.34k
OS Memory 12424.00k
Timestamp 06/13/2016 10:20:01 AM
NOTE: CALL EXECUTE generated line.
1 +;
2 +;
3 +;
4 +;
5 +;
6 +;
7 +;
8 +;
9 +;
3 + * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ;
* Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ;
4 + data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' ,
rule='||rule||' , rule_order='||rule_order||');'); run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
4:153
WARNING: Apparent symbolic reference ZIP5 not resolved.
4 +
;
&ZIP5 4
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.5
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.3
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.2
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3.1
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 3
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 2
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 1.5
WARNING: Apparent symbolic reference ZIP5 not resolved.
&ZIP5 1
NOTE: There were 9 observations read from the data set WORK.QUERYRULES.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
user cpu time 0.00 seconds
system cpu time 0.03 seconds
memory 714.34k
OS Memory 12424.00k
Timestamp 06/13/2016 10:20:01 AM
NOTE: CALL EXECUTE generated line.
1 +;
2 +;
3 +;
4 +;
5 +;
6 +;
7 +;
8 +;
9 +;
* Create ds of sampled Zips to use as macro parameters ;
data SampledZips;
input zip5 $;
datalines;
00926
02360
;
run;
* Build dataset from Oracle table that contains matching rules ;
proc sql;
connect to oracle as db (user=&orauser password=&orapass path="ivasprd");
create table QueryRules as
select * from connection to db
( select analysis_desc,
rule,
rule_order
from ivprl.analysis_ctrl
where trunc(upload_create_dt) = trunc(sysdate)
and rule_order not in(400)
and rule_order < 5
order by rule_order desc
);
disconnect from db;
quit;
%MACRO BuildZIP5QueryData(zip5= );
* RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ;
%MACRO BuildQueryData(analysis_desc= , rule= , rule_order= );
%PUT &ZIP5 &RULE_ORDER;
%MEND BuildQueryData;
* Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ;
data _null_;
set QueryRules;
call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;
%MEND BuildZIP5QueryData;
* Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset ;
data _null_;
set SampledZips;
call execute('%BuildZIP5QueryData(zip5='||zip5||');');
run;
.
Hooray!
372 proc sql;
373 create table WANT as
374 select A.ZIP5,
375 B.RULE_ORDER
376 from SAMPLEDZIPS A
377 full join QUERYRULES B
378 on 1 = 1;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: Table WORK.WANT created, with 18 rows and 2 columns.
379 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 1927.42k
OS Memory 17552.00k
Timestamp 06/13/2016 12:22:43 PM
380
381 %macro test(zip5= , rule_order= );
382 %put HERE &ZIP5 &RULE_ORDER;
383 %mend test;
384 data _null_;
385 set Want;
386 call execute('%test(zip5='||zip5||', rule_order='||rule_order||');');
387 run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
386:56
HERE 00926 4
HERE 00926 3.5
HERE 00926 3.3
HERE 00926 3.2
HERE 00926 3.1
HERE 00926 3
HERE 00926 2
HERE 00926 1.5
HERE 00926 1
HERE 02360 4
HERE 02360 3.5
HERE 02360 3.3
HERE 02360 3.2
HERE 02360 3.1
HERE 02360 3
HERE 02360 2
HERE 02360 1.5
HERE 02360 1
NOTE: There were 18 observations read from the data set WORK.WANT.
Hi @buechler66,
The late Roland Rashleigh-Berry recommended to mask macro calls in CALL EXECUTE with %NRSTR in order to prevent premature execution: http://www.datasavantconsulting.com/roland/call_ex
Your code example seems to be a case in point, because with
call execute('%nrstr(%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||'));');
and
call execute('%nrstr(%BuildZIP5QueryData(zip5='||zip5||'));');
the issue does not occur (at least in my test environment).
@buechler66 wrote:
This does suppress the error, but the &ZIP5 variable still does not resolve as needed.
Well, it resolved in my test environment. How did it resolve in your SAS session, if not "as needed," so that the warning message no longer occurs?
Log output below. It did Put the text 'HERE' followed by the Rule_Order, but &ZIP5 must be blank as it doesn't appear in the output.
For example:
HERE 4
Log output:
NOTE: CALL EXECUTE generated line.
1 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISMATCH
2 + , rule=NVL(A.ACTUAL_ENTRY_DATETIME,SYSDATE)
<> NVL(B.ACTUAL_ENTRY_DATETIME,SYSDATE)
3 +
4 +
5 +
6 +
7 + , rule_order= 4);
HERE 4
8 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISSING IN IV
9 + , rule=A.ACTUAL_ENTRY_DATETIME IS NULL AND
B.ACTUAL_ENTRY_DATETIME IS NOT NULL
10 +
11 +
12 +
13 +
14 + , rule_order= 3.5);
HERE 3.5
15 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN IV
16 + , rule=A.LAST_SCAN_DATETIME IS NULL AND
B.LAST_SCAN_DATETIME IS NOT NULL
17 +
18 +
19 +
20 +
21 + , rule_order= 3.3);
HERE 3.3
22 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN BIDS
23 + , rule=A.LAST_SCAN_DATETIME IS NOT NULL AND
B.LAST_SCAN_DATETIME IS NULL
24 +
25 +
26 +
27 +
28 + , rule_order= 3.2);
HERE 3.2
29 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN IV
30 + , rule=A.LAST_SCAN_DATETIME >
B.LAST_SCAN_DATETIME
31 +
32 +
33 +
34 +
35 + , rule_order= 3.1);
HERE 3.1
36 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN BIDS
37 + , rule=A.LAST_SCAN_DATETIME <
B.LAST_SCAN_DATETIME
38 +
39 +
40 +
41 +
42 + , rule_order= 3);
HERE 3
43 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISMATCH
44 + , rule=NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <>
NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)
45 +
46 +
47 +
48 +
49 + , rule_order= 2);
HERE 2
50 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE LATER IN IV
51 + , rule=A.ACTUAL_DLVRY_DATE >
B.ACTUAL_DLVRY_DATE
52 +
53 +
54 +
55 +
56 + , rule_order= 1.5);
HERE 1.5
57 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISSING IN IV
58 + , rule=A.ACTUAL_DLVRY_DATE IS NULL AND
B.ACTUAL_DLVRY_DATE IS NOT NULL
59 +
60 +
61 +
62 +
63 + , rule_order= 1);
HERE 1
2 + %BuildZIP5QueryData(zip5=02360 );
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
3:94
NOTE: There were 9 observations read from the data set WORK.QUERYRULES.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 715.50k
OS Memory 12680.00k
Timestamp 06/13/2016 11:52:54 AM
NOTE: CALL EXECUTE generated line.
1 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISMATCH
2 + , rule=NVL(A.ACTUAL_ENTRY_DATETIME,SYSDATE)
<> NVL(B.ACTUAL_ENTRY_DATETIME,SYSDATE)
3 +
4 +
5 +
6 +
7 + , rule_order= 4);
HERE 4
8 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISSING IN IV
9 + , rule=A.ACTUAL_ENTRY_DATETIME IS NULL AND
B.ACTUAL_ENTRY_DATETIME IS NOT NULL
10 +
11 +
12 +
13 +
14 + , rule_order= 3.5);
HERE 3.5
15 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN IV
16 + , rule=A.LAST_SCAN_DATETIME IS NULL AND
B.LAST_SCAN_DATETIME IS NOT NULL
17 +
18 +
19 +
20 +
21 + , rule_order= 3.3);
HERE 3.3
22 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN BIDS
23 + , rule=A.LAST_SCAN_DATETIME IS NOT NULL AND
B.LAST_SCAN_DATETIME IS NULL
24 +
25 +
26 +
27 +
28 + , rule_order= 3.2);
HERE 3.2
29 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN IV
30 + , rule=A.LAST_SCAN_DATETIME >
B.LAST_SCAN_DATETIME
31 +
32 +
33 +
34 +
35 + , rule_order= 3.1);
HERE 3.1
36 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN BIDS
37 + , rule=A.LAST_SCAN_DATETIME <
B.LAST_SCAN_DATETIME
38 +
39 +
40 +
41 +
42 + , rule_order= 3);
HERE 3
43 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISMATCH
44 + , rule=NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <>
NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)
45 +
46 +
47 +
48 +
49 + , rule_order= 2);
HERE 2
50 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE LATER IN IV
51 + , rule=A.ACTUAL_DLVRY_DATE >
B.ACTUAL_DLVRY_DATE
52 +
53 +
54 +
55 +
56 + , rule_order= 1.5);
HERE 1.5
57 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISSING IN IV
58 + , rule=A.ACTUAL_DLVRY_DATE IS NULL AND
B.ACTUAL_DLVRY_DATE IS NOT NULL
59 +
60 +
61 +
62 +
63 + , rule_order= 1);
HERE 1
You modified the inner macro and introduced a macro parameter ZIP5, which is not populated. This "most local" definition of ZIP5 shadows the parameter ZIP5 of the outer macro, whose value you expect to see.
This is a complex version of a known theme involving CALL EXECUTE. When a DATA step generates CALL EXECUTE statements, the generated statements run as soon as possible. For macro language statements, that means they run right away. For SAS language statements, such as the generated DATA _NULL_ step, that means they have to wait. They stack up and run later, once the DATA step that contains the original CALL EXECUTE statements has finished.
That's complicated enough, but you have a situation that's harder. The same principles apply when macros call macros. The macro calls run right away, such as %BuildZIP5QueryData. However, the DATA _NULL_ step that is generated along the way (the one with the SET QUERYRULES statement) cannot run yet. So it waits and runs later. By the time it runs, the macro %BUILDZIP5QueryData has finished running, and so its symbol table including &ZIP5 has been erased.
There are ways to get around this using %NRSTR, to delay the execution of macro statements generated by CALL EXECUTE. Because of the complexity of nested macros, I would recommend a different approach. Instead of using CALL EXECUTE, have the DATA steps write the macro calls to a file. Then at the end, %INCLUDE the file. It becomes a lot easier to follow that way.
I would suggest not using call execute and macro at the same time. To be honest, there isn't really a need, in fact there is almost never a need for macro in the first place. If you post what you have and what you want I can suggest better, but I don't have time to go through that mass of post.
Hi. Sorry about the length of the post. This is what I have, note the '%PUT HERE &ZIP5 &RULE_ORDER' statement.
%MACRO BuildZIP5QueryData(zip5= );
* RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ;
%MACRO BuildQueryData(zip5= , analysis_desc= , rule= , rule_order= );
%PUT HERE &ZIP5 &RULE_ORDER;
%MEND BuildQueryData;
* Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ;
data _null_;
set QueryRules;
call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;
%MEND BuildZIP5QueryData;
* Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset ;
data _null_;
set SampledZips;
call execute('%BuildZIP5QueryData(zip5='||zip5||');');
run;
My desire is to have &ZIP5 resolve inside the nested macro that would resolve to my %Put statement outputting the following:
ZIP5 Rule_Order
00926 1
00926 2
00926 3
00926 4
00926 5
00926 6
00926 7
00926 8
00926 9
02360 1
02360 2
02360 3
02360 4
02360 5
02360 6
02360 7
02360 8
02360 9
Yes, and this is my point. You have a datastep generating the macro calls - the datastep is a loop, so whay do you need the macro call and a sub macro etc. It seems to be vastly over complicated. To get your output:
data _null_; set SampledZips; by zip5; if first.zip5 then rule_order=1; else rule_order=rule_order+1; %put _all_; run;
There is no need in the above for a macro call or a call execute? Don't try to over complicate things, keep it really, really simple. If you provide a small amount of text data (form of datastep) and what the output should be - not it calls a macro which calls a macro, what the end product should be, can suggest further.
The SampledZips dataset looks like this:
ZIP5
00926
02360
The QueryRules dataset looks like this:
Rule_Order
1
2
3
4
5
6
7
8
9
The desired output to my log in the Put statement is:
ZIP5 Rule_Order
00926 1
00926 2
00926 3
00926 4
00926 5
00926 6
00926 7
00926 8
00926 9
02360 1
02360 2
02360 3
02360 4
02360 5
02360 6
02360 7
02360 8
02360 9
No need for any of that, just use a join all to all (forget what the term is now):
proc sql; create table WANT as select A.ZIP5, B.RULE_ORDER from ZIP5 A full join RULE_ORDER B; quit;
You now have a dataset with the information you require. Note, you might need: on 1=1;
See my update, add:
full join QUERYRULES B
on 1=1;
Hooray!
372 proc sql;
373 create table WANT as
374 select A.ZIP5,
375 B.RULE_ORDER
376 from SAMPLEDZIPS A
377 full join QUERYRULES B
378 on 1 = 1;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: Table WORK.WANT created, with 18 rows and 2 columns.
379 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 1927.42k
OS Memory 17552.00k
Timestamp 06/13/2016 12:22:43 PM
380
381 %macro test(zip5= , rule_order= );
382 %put HERE &ZIP5 &RULE_ORDER;
383 %mend test;
384 data _null_;
385 set Want;
386 call execute('%test(zip5='||zip5||', rule_order='||rule_order||');');
387 run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
386:56
HERE 00926 4
HERE 00926 3.5
HERE 00926 3.3
HERE 00926 3.2
HERE 00926 3.1
HERE 00926 3
HERE 00926 2
HERE 00926 1.5
HERE 00926 1
HERE 02360 4
HERE 02360 3.5
HERE 02360 3.3
HERE 02360 3.2
HERE 02360 3.1
HERE 02360 3
HERE 02360 2
HERE 02360 1.5
HERE 02360 1
NOTE: There were 18 observations read from the data set WORK.WANT.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.