topic Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro in SAS Enterprise Guide

WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 02:30:26 GMT

Hello,

Why would I be getting the message:

WARNING: Apparent symbolic reference TABLE_SIZE not resolved.

When I run this code (from within a macro):

proc sql noprint;

select count(*) into :table_size from table_name;

quit;

Kind regards,

Mark

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

kiranv_ — Fri, 04 May 2018 02:42:06 GMT

your table_name table is empty. it has nothing to do with inside or outside of macro. When your table is empty no value is passed to macrovariable and hence your macro variable is not created.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

SASKiwi — Fri, 04 May 2018 02:47:37 GMT

It is good practice to do this to avoid the warning:

%let table_size=;

proc sql noprint;
select count(*) into :table_size from table_name;
quit;

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

Astounding — Fri, 04 May 2018 03:35:21 GMT

Another thing to consider ...

If SQL is creating the macro variable &TABLE_SIZE, it will always create it in the local symbol table. If you refer to &TABLE_SIZE outside of the macro, it will be gone and you would expect to see the message you got.

Fixing that is easy. Add this just before the PROC SQL statement:

%global table_size;

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 03:40:09 GMT

Thank you SASKiwi

I am running this code within a macro where I am passing the table_name in as a variable so technically I am using:

%let table_size=;

proc sql noprint;
select count(*) into :table_size from &table_name.;
quit;

However, I still can't obtain the table_size, it continues to be unresolved (still nothing in it when I print it) whereas it should come back as 3 as there are 3 rows in the table.

Ultimately, all I want is to know within a macro is how many rows are in table_name. (the variable passed to the table) and store the row count in a variable which I can use throughout the same macro.

Note: the macro is able to successfully determine the correct value of table_name, it just can't count the rows.

Any other suggestions would be so appreciated.

Kind regards

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 03:42:40 GMT

Thank you for this, I don't need to use it outside the macro but this is great info thank you

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

SASKiwi — Fri, 04 May 2018 03:46:28 GMT

I suggest you replace &table_name in the macro with the actual table name as a test.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 03:51:53 GMT

Thank you, I've just did that but still getting the same warning message. The table is identified it seems but it can't count the rows. Kind regards

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 03:56:13 GMT

Thank you kiranv_ , I am passing the table_name into the macro, and it seems to be recognised OK, sorry I should have said before.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

SASKiwi — Fri, 04 May 2018 04:01:04 GMT

Is it a SAS table or some other database table? If not a SAS table try a SAS table;

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 04:08:18 GMT

Thank you, it is a SAS table. Can the proc sql run within a IF THEN DO; and END; (within a macro)?

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

SASKiwi — Fri, 04 May 2018 04:12:19 GMT

No, you have to convert that to macro code like %if ....... %then %do;...... %end; If that is part of your macro, remove it to get it working, then try putting the code back as macro code.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

Kurt_Bremser — Fri, 04 May 2018 04:43:29 GMT

Post the whole log of the macro. There's either a problem with &table_name or something else is going wrong.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 05:51:29 GMT

MLOGIC(APPLY_CONTROL_TABLE): %LET (variable name is TABLE_SIZE )
MPRINT(APPLY_CONTROL_TABLE):   proc sql noprint;
SYMBOLGEN: Macro variable TABLE_NAME resolves to the_table_name
MPRINT(APPLY_CONTROL_TABLE):   select count(*) into: TABLE_SIZE from the_table_name;
MPRINT(APPLY_CONTROL_TABLE):   quit;
MLOGIC(APPLY_CONTROL_TABLE): %PUT &=TABLE_SIZE
SYMBOLGEN: Macro variable TABLE_SIZE resolves to
TABLE_SIZE=

Thank you, above is the log.

If I run this on its own (Run Selection):

proc sql noprint; select count(*) into: TABLE_SIZE from table_name; quit;

then it works (I get TABLE_SIZE=3)

BUT ... running it within the macro it does not come back with anything at all in TABLE_SIZE. It is so confounding.

Do you see any problem with what I am going?

Kind regards

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

Kurt_Bremser — Fri, 04 May 2018 06:15:35 GMT

How are you calling the macro? If you do it with call execute, there might be a timing problem.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 07:10:04 GMT

Thank you, that sounds tricky and interesting. Do you know a way around that? Kind regards

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

Kurt_Bremser — Fri, 04 May 2018 10:53:12 GMT

IF (a really important if) you call a macro with call execute, you need to delay the execution of macro statements until the also contained proc/data steps can run.

call execute('%nrstr(%macro(parameters))');

In your log I see no messages from the proc sql, which tells me it was not executed before the %put.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

markc — Fri, 04 May 2018 14:28:48 GMT

Thank you for this, in my example the proc sql would be within the %macro above, does %nrstr force each line within %macro to complete before moving onto the next one, is that what you mean? Kind regards

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

Kurt_Bremser — Fri, 04 May 2018 20:03:38 GMT

The %nrstr prevents the resolution of the macro trigger when the code is pushed to the execution stack.

Without %nrstr, the macro statements (eg the %put) are resolved immediately, while the proc sql has to wait until the data step with the call execute has finished.

Re: WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

SASKiwi — Fri, 04 May 2018 23:39:41 GMT

Your macro log has: MPRINT(APPLY_CONTROL_TABLE): select count(*) into: TABLE_SIZE from the_table_name;

Isn't that supposed to be table_name?