BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwhite
Quartz | Level 8

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

16 REPLIES 16
PaigeMiller
Diamond | Level 26
What are the values of &lastDeptKey and &lasttoDeptKey and &validToDate just before the macro tries to execute this statement?
--
Paige Miller
Astounding
PROC Star

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

jwhite
Quartz | Level 8

Looking at it, yes, that seems very possible.

 

I guess more graceful handling is in order.

Astounding
PROC Star

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.

jwhite
Quartz | Level 8

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;

Astounding
PROC Star

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.

jwhite
Quartz | Level 8

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.

Astounding
PROC Star

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

jwhite
Quartz | Level 8

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.

 

Astounding
PROC Star

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.

jwhite
Quartz | Level 8

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?

 

Astounding
PROC Star

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.

Reeza
Super User
%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?
jwhite
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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