WARNING: Apparent symbolic reference TABLE_SIZE not resolved within macro

[markc]

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

[Kurt_Bremser]

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.

[markc]

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

[Kurt_Bremser]

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.

[markc]

Thank you KurtBremser

I'm still really struggling even though I've put in the %nrstr.

My ultimate goal is to determine the number of rows within a SAS dataset ultimately and I am happy to use any method. The existing program is using a PROC SQL to count rows within a macro, but maybe passing the number of rows into the macro would be fine too, but then I am still left with the same issue I think.

Ultimately I am processing 6 tables, so I pass in the table name to a macro so it knows which one to use, but the macro also needs to know the number of rows of the table before it can process it, so this PROC SQL is within that macro.

When I call the macro do I need to use a call execute if the call is within a data setup (1) but not within a macro vs. (2) within a macro? Should the PROC SQL be taken out of the macro and moved a level up where the number of rows is just passed in as a variable? Is there a SAS function which simply returns number of rows in a table (I would have thought this was would be such a basic function and need, but I can't seem to locate one).

Kind regards and thank yoU!

[Kurt_Bremser]

So I created some test-code from your snippet as a proof of concept:

%macro size_mac(table_name);

%let table_size=;

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

%put table_size=&table_size;
%mend;

%size_mac(sashelp.class);

The log from this shows it's working:

24         %macro size_mac(table_name);
25         
26         %let table_size=;
27         
28         proc sql noprint;
29         select count(*) into :table_size from &table_name;
30         quit;
31         
32         %put table_size=&table_size;
33         %mend;
34         
35         %size_mac(sashelp.class);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

table_size=      19

Next, two calls with call execute:

data _null_;
call execute('%size_mac(sashelp.class)');
call execute('%nrstr(%size_mac(sashelp.class))');
run;

24         data _null_;
25         call execute('%size_mac(sashelp.class)');
26         call execute('%nrstr(%size_mac(sashelp.class))');
27         run;

table_size=
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: CALL EXECUTE generated line.
1         + proc sql noprint;
1         +                   select count(*) into :table_size from sashelp.class;
1         +                                                                        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

2         + %size_mac(sashelp.class)
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

table_size=      19

The first one fails because of the timing, the second works.

For further research, complete code and logs will be necessary, as the problem has to originate elsewhere.

[SASKiwi]

Here is another way to find the rows in a SAS dataset without SQL:

%macro row_count (tablename =);
  %* OBS method uses SCL to open a table, get the row count, close it and return the row count
     so it works like a function.;
    %local dsid obs; 
    %let dsid = %sysfunc(open(&tablename)); 
    %if &dsid %then %let obs = %sysfunc(attrn(&dsid, nlobs)); 
    %let tmp_varlist = &obs;
    %let dsid = %sysfunc(close(&dsid)); 
    &obs
%mend row_count; 

%let rows = %row_count(tablename = sashelp.class);
%put rows = &rows;

[SASKiwi]

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?

[markc]

Yes, sorry, the actual code I am using is more specific but I was making more general here, but should have omitted the the_