topic Re: WARNING: Apparent symbolic reference in SAS Programming

WARNING: Apparent symbolic reference

jwhite — Mon, 26 Oct 2015 15:41:41 GMT

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!

Re: WARNING: Apparent symbolic reference

PaigeMiller — Mon, 26 Oct 2015 15:45:30 GMT

What are the values of &lastDeptKey and &lasttoDeptKey and &validToDate just before the macro tries to execute this statement?

Re: WARNING: Apparent symbolic reference

Astounding — Mon, 26 Oct 2015 15:49:56 GMT

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

Re: WARNING: Apparent symbolic reference

Reeza — Mon, 26 Oct 2015 17:29:14 GMT

%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?

Re: WARNING: Apparent symbolic reference

jwhite — Mon, 26 Oct 2015 19:10:43 GMT

Looking at it, yes, that seems very possible.

I guess more graceful handling is in order.

Re: WARNING: Apparent symbolic reference

Astounding — Mon, 26 Oct 2015 20:25:09 GMT

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.

Re: WARNING: Apparent symbolic reference

jwhite — Tue, 27 Oct 2015 17:18:14 GMT

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;

Re: WARNING: Apparent symbolic reference

jwhite — Tue, 27 Oct 2015 17:49:41 GMT

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

Re: WARNING: Apparent symbolic reference

Tom — Tue, 27 Oct 2015 18:16:05 GMT

What is the overall purpose of this block of code? Why not just process the table by AdmUnit instead of running separate queries for each AdmUnit?

Re: WARNING: Apparent symbolic reference

Tom — Tue, 27 Oct 2015 19:58:23 GMT

Look carefully at your logic around this comparison:

&validToDate="01JAN5999"

If the variable that is used to store into the macro variable VALIDTODATE is a date value the result will depend of the format attached. So it could be a string of digits or the string 01JAN5999 or something else if a different format is attached. But it will never have quotes around it like your test is looking for.

You might want to just add the quotes to both sides of the comparison.

"&validToDate"="01JAN5999"

Re: WARNING: Apparent symbolic reference

Astounding — Tue, 27 Oct 2015 22:58:27 GMT

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.

Re: WARNING: Apparent symbolic reference

jwhite — Wed, 28 Oct 2015 14:06:11 GMT

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.

Re: WARNING: Apparent symbolic reference

Astounding — Wed, 28 Oct 2015 14:54:25 GMT

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 ...;

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).

Re: WARNING: Apparent symbolic reference

jwhite — Wed, 28 Oct 2015 15:52:30 GMT

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.

Re: WARNING: Apparent symbolic reference

Astounding — Wed, 28 Oct 2015 16:09:08 GMT

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.

Re: WARNING: Apparent symbolic reference

jwhite — Wed, 28 Oct 2015 18:53:52 GMT

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?

Re: WARNING: Apparent symbolic reference

Astounding — Wed, 28 Oct 2015 19:06:13 GMT

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.