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!
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.
Is it possible that zero observations satisfy the WHERE conditions in the SQL step?
Looking at it, yes, that seems very possible.
I guess more graceful handling is in order.
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.
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;
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.
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.
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).
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.
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.
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?
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.
I used PUT and found the values of lastDeptKey, lastToDeptKey and validToDate to be empty.
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.