BookmarkSubscribeRSS Feed
markc
Obsidian | Level 7

 

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

23 REPLIES 23
kiranv_
Rhodochrosite | Level 12

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
Obsidian | Level 7
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
PROC Star

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
Obsidian | Level 7

Thank you  

 

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
PROC Star

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
Obsidian | Level 7
Thank you for this, I don't need to use it outside the macro but this is great info thank you
SASKiwi
PROC Star

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

markc
Obsidian | Level 7

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
PROC Star

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

markc
Obsidian | Level 7

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

SASKiwi
PROC Star

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.

markc
Obsidian | Level 7

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

 

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 23 replies
  • 1420 views
  • 18 likes
  • 5 in conversation