DATA Step, Macro, Functions and more

WARNING: Apparent symbolic reference

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

WARNING: Apparent symbolic reference

Good Morning, All.

 

I'm a new SAS developer and seem to be hitting a snag with a macro.

 

I'm getting the warnings:

WARNING: Apparent symbolic reference LASTDEPTKEY not resolved.

WARNING: Apparent symbolic reference LASTTODEPTKEY not resolved.

and error:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999"

 

Here's the macro:

 

    %let i=1;
    %let admUnit=%scan(&admUnits,1);
    %let fice=%scan(&ficeCodes,1);

    %do %while ( &admUnit > 0 );
        %let x = 1;
        %let admUnitQry = &admUnit;
        %do %until ( (&x = 0) or (&x = 50) ) ;
            /*-- Loop through rows in crossing table for the
                    adm_unit and move to adm units until
                    the valid to date is '01JAN5999'd
                    (last adm unit in the group - most current department) --*/
            proc sql noprint;
                select b.department_key, b.to_department_key,
                       b.valid_to_date, c.administrative_unit_no
                        into :lastDeptKey, :lastToDeptKey, :validToDate, :lastAdmUnit
                    from target.department_dim a, target.dept_key_x_to_dept_key_xing b,
                         target.department_dim c
                    where a.administrative_unit_no=&admUnitQry
                      and a.fice_code=&fice
                      and a.department_key=b.department_key
                      and c.department_key=b.to_department_key;
            quit;
                
            %if &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999" %then  
                %let x = 0;
            %else %do;  
                %let x = %eval(&x + 1);
                %let admUnitQry = &lastAdmUnit;
            %end;

        %end;        /* do until most current adm/dept is found */

 

I've used %put statements, but can't seem to find an error. Also, I've checked the datasets that these variables are pulling data from and the data appears good. I figure there's got to be something in the syntax. NOTE: the above isn't _all_ of the macro code, but a snippet that appears relevant.

 

Thanks!

 


Accepted Solutions
Solution
‎11-03-2015 02:17 PM
Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

Yes ... just remove the semicolon after the lone word %else;

 

Also note that %PUT does not require quotes to print text.  More simply:

 

%if &sqlobs=0 %then
                %put Admin Unit Not Found. Admin Unit: &admUnitQry  Fice: &fice;
            %else
                %if &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999" %then
                    %let x = 0;

 

Then the final %ELSE of course.

 

 

If the error persists, we can still get rid of it.  But let's see what happens with this much.

Good luck.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,931

Re: WARNING: Apparent symbolic reference

What are the values of &lastDeptKey and &lasttoDeptKey and &validToDate just before the macro tries to execute this statement?
Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

Is it possible that zero observations satisfy the WHERE conditions in the SQL step?

Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

Posted in reply to Astounding

Looking at it, yes, that seems very possible.

 

I guess more graceful handling is in order.

Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

In this case, it's easy to be graceful.  SQL creates an automatic macro variable showing how many records it retrieved.  So you can add immediately after the QUIT statement:

 

%if &sqlobs=0 %then ...

 

Then continue with the other possible conditions currently being tested.

 

Good luck.

Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

Posted in reply to Astounding

So, after the Quit statement, something like:

 

    %if &sqlobs=0 %then
                %put 'Admin Unit Not Found. Admin Unit:' &admUnitQry 'Fice:' &fice;
            %else;
                %if &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999" %then
                    %let x = 0;
                %else %do;  
                    %let x = %eval(&x + 1);
                    %let admUnitQry = &lastAdmUnit;
                %end;

Solution
‎11-03-2015 02:17 PM
Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

Yes ... just remove the semicolon after the lone word %else;

 

Also note that %PUT does not require quotes to print text.  More simply:

 

%if &sqlobs=0 %then
                %put Admin Unit Not Found. Admin Unit: &admUnitQry  Fice: &fice;
            %else
                %if &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999" %then
                    %let x = 0;

 

Then the final %ELSE of course.

 

 

If the error persists, we can still get rid of it.  But let's see what happens with this much.

Good luck.

Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

Posted in reply to Astounding

When I take the semicolon away from the lone %ELSE statement, and run the code...it seems to go forever. I have to stop the program. That should be the case as there's only about 324 rows it's going through.

 

With the semicolon, it seems to go through them all, but still error out when there's no match.

Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

Well, removing the semicolon is the right thing to do.  "Going on forever" is a sign that one of your loops (%do %while, or %do %until) doesn't meet its ending condition.  At a minimum, change the &SQLOBS logic to look like this:

 

%if &sqlobs=0 %then %do;

   %let x=0;

   %put ... as before ...;

%end;

%else ... as before ...;

%else ... as before ...;

 

That will guarantee that the interior loop ends.  It would be safer to review the exterior loop for the same sort of issue (making sure that it will have an ending).

Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

Posted in reply to Astounding

That did the trick, thanks!

 

The macro then bombs later at the next reference to &lastAdmUnit as:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. 

 

The code is looking to create a new table using &lastAdmUnit as adm_unit. I'd think that even if &lastAdmUnit were blank, adm_unit could be create...it'd just be empty.

 

Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

You can force macro variables to exist.  Just add this as the beginning of your macro:

 

%local LastAdmUnit;

 

You can do the same for other macro variables as well ... or you can use %GLOBAL instead of %LOCAL if the macro variables are supposed to remain in existence after your macro finishes executing.

 

There are a couple of cautions, however.  First, by forcing the macro variable to exist, that does not change the data within any table or data set.  You have to make sure that the SAS code is correct when a blank is substituted for the macro variable reference.  Second, if there are zero observations retrieved within an interation of your looping logic, the macro variables will not be blank.  Instead, they will remain at whatever value they had from the previous loop iteration.  You could force them to be blank by adding at the beginning of your loop:

 

%let LastAdmUnit=;

 

I'm losing sight of the result you want, but it sounds like you are getting closer.

Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

Posted in reply to Astounding

Perfect!

 

To help me better understand this next time, I recieved the initial warning and error because there was no value due to no match? I would have thought the original if/else statement would have been able to handle it. The first condition wouldn't be met, so it would drop into the second. Why was that not the case?

 

Super User
Posts: 5,513

Re: WARNING: Apparent symbolic reference

When 0 records satisfy the WHERE condition, SQL doesn't do anything.  In particular, it doesn't create your macro variables.

 

After that, SAS sees:

 

%if &lastDeptKey=&lastToDeptKey ...

 

It looks for the macro variables, and they just don't exist.  It's not that they have a null value, they just don't exist at all.  That's what caused your original error message.

 

You can always add diagnostic statements, such as:

 

%put _user_;

 

That will help give you a feel for how the software is working.

 

In this case, it might have been too big a leap originally to figure out which diagnostic statements to add.  Practice will get you there!

 

Good luck.

Super User
Posts: 19,822

Re: WARNING: Apparent symbolic reference

%if &lastDeptKey=&lastToDeptKey or &validToDate="01JAN5999" %then

What value is stored in ValidToDate? My guess is it's a date variable and may not be in the format you expect above so the comparison is invalid. Just a guess though. Have you tried running your macro with MPRINT and SYMBOLGEN on to try and debug it?
Frequent Contributor
Posts: 89

Re: WARNING: Apparent symbolic reference

I used PUT and found the values of lastDeptKey, lastToDeptKey and validToDate to be empty.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 847 views
  • 0 likes
  • 5 in conversation