How can I call a variable from a table that is a macro variable?

apologies if my title was confusing to read, but I am not aware of how else to describe it briefly.

I am trying to call a variable from a table that is a macro variable (The table is a macro variable)

My macro looks like this:

%macro genre_analysis(table1=,table2=,genre=,genre1=);
proc sql;
create table &table1 as
select id, original_title, genres, revenue
from genres_revenue
where genres_revenue.genres like &genre
and revenue is not null
group by id
having revenue ne 0
;
quit;

proc sql;
create table &table2 as
select avg(revenue) as Average format=dollar16.2, median(revenue) as Median format=dollar16.2, std(revenue) as std format=dollar16.2
from &table1;
quit;

Everything works fine until I get to this part of the macro:

proc sql;
title "Revenue Stats by Genre";
    insert into genre_summary
    set Genre=&genre1,
    average=&table2.average,
    median=&table2.median,
    std=&table2.std;
%mend genre_analysis;

I am trying to insert a row into a table I create outside of the macro. But using "&table2.average" and the 2 others that starts with "&table2" does not call the variables from the table I created in the macro.

For example:

%genre_analysis(table1=horror_revenue,table2=horror_revenue_stats,genre='%Horror%',genre1='Horror')

Returns:

NOTE: Table WORK.HORROR_REVENUE created, with 725 rows and 4 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


NOTE: Table WORK.HORROR_REVENUE_STATS created, with 1 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.
**ERROR: The following columns were not found in the contributing tables:
       horror_revenue_statsaverage, horror_revenue_statsmedian, horror_revenue_statsstd.**

I have been focusing on the Error that I starred, as I believe that is where the issue is.

I tried using a "from" clause but that does not seem to work either.

Any assistance or suggestions would be appreciated!

Solution

You don't need to store a selection of summary statistics in an intermediate table for later use in an INSERT statement. Instead, you can insert the selection directly into the table.

For example:

* table to receive summary computations;
proc sql;
  create table stats
  (age num
  , average num
  , median  num
  , std     num
  , N num
  );

* insert summary computations directly;    
proc sql;
  insert into stats
  select 
     age
   , mean (height) 
   , median (height)
   , std (height)
   , count (height) 
   from sashelp.class
   group by age
  ;

* insert more summary computations directly;

For the case of having new-results in one table that need to be added to collecting-table you can do

proc append base=<collecting-table> data=<new_results>;

insert into <collecting-table> select * from <new-results>;

Finally, for the case of having some new result values in macro variables themselves, you can insert those values with a VALUE clause. You have literal quote any macro variable resolution that will be mapped to a character column.

insert into <collecting-table> values ("&genre", &genre.mean, ...);