WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

[markc]

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

[kiranv_]

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.

[markc]

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.

[SASKiwi]

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;

[markc]

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

[Astounding]

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;

[markc]

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

[SASKiwi]

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

[markc]

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

[SASKiwi]

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

[markc]

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

[SASKiwi]

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.

[Kurt_Bremser]

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

[markc]

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

[Kurt_Bremser]

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